The following SQL script will generate a single random number: [cc lang=”sql”] SELECT CAST(1000000-CEILING(RAND()* 899001) + (DATEPART(ss, GETDATE()) * 1000 ) + DATEPART(ms, GETDATE()) as int) [/cc]
Continue reading ...One of my favorite aspects of schemas (which were introduced in 2005) is the ability easily manage permissions. For those getting caught in terminology, schemas are the new abstraction layer that replace object owners. In SQL 2000, every object had an owner. And that owner was a user. In most cases the user was dbo. So your stored procedures would have a naming convention of dbo.spExecuteMe. With schemas, the model has changed. While object owners were actual users, schemas are not. Schemas are abstract objects used to separate owners from the objects. The beauty of this is that you can assign a user access to an entire schema, which may be comprised of hundreds of objects. Instead of assigning permissions at the object level, you can say, “Any user assigned to the role of ‘ExecApp’ will be able to execute any object that is created within the schema ‘App’. This way, by simply adding a user to the ExecApp role, he will inherently be able to execute any procedure in the application schema. Likewise, any procedure created within the schema ‘App’, will automatically be able to execute that procedure. So how do you do it? Start by Creating a schema. [cc lang=”sql”] CREATE SCHEMA App [/cc] Next, transfer stored procedures to this schema, or simply create stored procedures with this schema. To transfer: [cc lang=”sql”] ALTER SCHEMA App TRANSFER dbo.spGetCustomers [/cc] Lastly, create a role that will be allowed to execute procedures in the App schema; Add a user to […]
Continue reading ...The following shows how to find the version of SQL Server you are running (described as productversion). It also shows how to query the level of the product. The level of the product indicates whether it was the initial release, a service pack, or a beta version: RTM = shipping version SPn = service pack (where n is the version) CTP = Community Technology Preview version [cc lang=”sql”] SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’) [/cc] If the above statement does not work, you may be running SQL Server 7.0. In which case, use the following statement: [cc lang=”sql”] SELECT @@VERSION [/cc]
Continue reading ...Determining the database owner is important if you want to take advantage of cross-database-ownership-chaining. If databases have different owners, then you have issues with accessing objects between databases. To find the database owners: [cc lang=”sql”] SELECT SUSER_SNAME(owner_sid) FROM sys.databases [/cc] To change the owner of a database: [cc lang=”sql”] USE database EXEC sp_changedbowner ‘sa’ [/cc] The standard owner for databases is usually sa.
Continue reading ...The following query will compare the nonclustered indexes vs the clustered index and determine which index would qualify as the best clustered index based upon the DMV statistics. If a clustered index does not exist on the table, it will also suggest one of the existing nonclustered indexes as the clustered index. [cc lang=”sql”] DECLARE @NonClusteredSeekPct float DECLARE @ClusteredLookupFromNCPct float — Define percentage of usage the non clustered should — receive over the clustered index SET @NonClusteredSeekPct = 1.50 — 150% — Define the percentage of all lookups on the clustered index — should be executed by this non clustered index SET @ClusteredLookupFromNCPct = .75 — 75% SELECT TableName = object_name(idx.object_id) ,NonUsefulClusteredIndex = idx.NAME ,ShouldBeClustered = nc.NonClusteredName ,Clustered_User_Seeks = c.user_seeks ,NonClustered_User_Seeks = nc.user_seeks ,Clustered_User_Lookups = c.user_lookups ,DatabaseName = db_name(c.database_id) FROM sys.indexes idx LEFT JOIN sys.dm_db_index_usage_stats c ON idx.object_id = c.object_id AND idx.index_id = c.index_id –AND c.database_id = @DBID JOIN ( SELECT idx.object_id ,nonclusteredname = idx.NAME ,ius.user_seeks FROM sys.indexes idx JOIN sys.dm_db_index_usage_stats ius ON idx.object_id = ius.object_id AND idx.index_id = ius.index_id WHERE idx.type_desc = ‘nonclustered’ AND ius.user_seeks = ( SELECT MAX(user_seeks) FROM sys.dm_db_index_usage_stats WHERE object_id = ius.object_id AND type_desc = ‘nonclustered’ ) GROUP BY idx.object_id ,idx.NAME ,ius.user_seeks ) nc ON nc.object_id = idx.object_id WHERE idx.type_desc IN (‘clustered’,’heap’) — non clustered user seeks outweigh clustered by 150% AND nc.user_seeks > (c.user_seeks * @NonClusteredSeekPct) — nc index usage is primary cause of clustered lookups 80% AND nc.user_seeks >= (c.user_lookups * @ClusteredLookupFromNCPct) ORDER BY nc.user_seeks DESC [/cc] The way it performs this determination is […]
Continue reading ...
