Creating an index on a table variable can be done implicitly within the declaration of the table variable by defining a primary key and creating unique constraints. The primary key will represent a clustered index, while the unique constraint a non clustered index. DECLARE @Users TABLE ( UserID INT PRIMARY KEY, UserName varchar(50), UNIQUE (UserName) [...]
Archive of entries posted on November 2009
Create Index on Temp Table
One of the most valuable assets of a temp table (#temp) is the ability to add either a clustered or non clustered index. Additionally, #temp tables allow for the auto-generated statistics to be created against them. This can help the optimizer when determining cardinality. Below is an example of creating both a clustered and non-clustered [...]
Random Number
The following SQL script will generate a single random number: SELECT CAST(1000000-CEILING(RAND()* 899001) + (DATEPART(ss, GETDATE()) * 1000 ) + DATEPART(ms, GETDATE()) as int)
Simplifying Security Using Schemas
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. [...]
What Version of SQL Server Am I Running?
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 [...]
Determine Database Owner
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: SELECT SUSER_SNAME(owner_sid) FROM sys.databases To change the owner of a database: USE database EXEC sp_changedbowner ‘sa’ The standard owner for databases is [...]
Find Best Clustered Index
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. DECLARE @NonClusteredSeekPct float [...]
Creating a Rules Engine
A rules engine is a schedule based data validity application that typically runs as a meta-layer on top of an OLTP application. It fires a set of queries (defined as rules) which determine whether the underlying data elements comply with a specific rule’s definition. The compliance to the rule is then recorded indicating a pass [...]
