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 index on a temp table. [cc lang=”sql”] CREATE TABLE #Users ( ID int IDENTITY(1,1), UserID int, UserName varchar(50) ) INSERT INTO #Users ( UserID, UserName ) SELECT UserID = u.UserID ,UserName = u.UserName FROM dbo.Users u CREATE CLUSTERED INDEX IDX_C_Users_UserID ON #Users(UserID) CREATE INDEX IDX_Users_UserName ON #Users(UserName) [/cc] Even though you can implicitly create a clustered index on a table variable (@table) by defining a primary key or unique constraint, it is generally more efficient to use a temp table. The downside is recompilation, however this can be suppressed with the use of (Keep Plan) or (Keep Fixed Plan) hints. Continue reading ...

Random Number

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]

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. 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 ...

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 (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]