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.

10 comments
Nitin 29 Aug 2015 at 11:08 am

Hi Rob. We do have standby srevers on hand at all times, and we are using one of those srevers to recover from this outage as we speak. The reason this outage is taking longer than a typical hardware failure is that the data on RAID array in this server is corrupted, and we’ve had to resort to restoring from a fresh backup of the volume. This takes quite a while, since the volume is restored block-by-block. One of the affected serves is now back on-line (sugarhouse) and the other is at 80% on the restore (it has a larger amount of data to restore). We expect the other server (bullwhackers) to be back on-line this evening probably in about three hours.We’re sorry for the inconvenience this has caused but we are working to restore service as fast as possible, and will be working around the clock until the restore is complete.

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

Oscar 29 Aug 2015 at 8:41 am

Thanks, Kerry, for the encouragement. I’m punsuirg print publishers now, and when there’s news to share, I’ll post it here. Whatever happens on the print front, Feet to Follow will definitely be made available as an Accordance module.

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

  • Thanks for visiting!