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.

    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)

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.

8 comments
ryan 29 Sep 2014 at 3:09 pm

The clearest example I’ve seen of this. Well done. I just finished implementing it and it worked like a champ.

Morgan 22 May 2014 at 12:51 pm

Worked a treat. Was doing self joins with temp tables. Without the index it was 10 times slower….literally

snigdha 15 Nov 2013 at 8:11 pm
TN 18 Sep 2013 at 9:16 am

AT WHAT INSTANCE WE SHOULD USE INDEX. TELL ME A SCENARIO WHERE WE CAN USE CLUSTER INDEX

Ajay Khatri 14 Mar 2012 at 1:24 am

Great ………..!!!!!!!!!!!!!!!!!!

Nick Carter 05 Mar 2012 at 10:40 am

The information is helpful…thanks

but where is the +1 button

Nian 21 Nov 2011 at 5:20 am

Hi Derek,

How about creating index immediately after table declaration? Does it eliminate recompilation?

Derek 15 Dec 2011 at 10:39 am

Nah, you can’t count on that as mitigating recompilation. It actually has to do with how much data was in the table at the time of compilation. If that changes significantly in a subsequent execution, it will recompile. The precise descriptions of the data changes are published somewhere.

Featured Articles

 Site Author