Create Index on Table Variable

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

The drawback is that the indexes (or constraints) need to be unique. One potential way to circumvent this however, is to create a composite unique constraint:

    DECLARE @Users TABLE
    (
        UserID  INT PRIMARY KEY,
        UserName VARCHAR(50),
        FirstName VARCHAR(50),
        UNIQUE (UserName,UserID)
    )

You can also create the equivalent of a clustered index. To do so, just add the clustered reserved word.

    DECLARE @Users TABLE
    (
        UserID  INT PRIMARY KEY,
        UserName VARCHAR(50),
        FirstName VARCHAR(50),
        UNIQUE CLUSTERED (UserName,UserID)
    )

Generally, temp tables perform better in situations where an index is needed. The downfall to temp tables is that they will frequently cause recompilation. This was more of an issue with SQL 2000 when compilation was performed at the procedure level instead of the statement level. SQL 2005 and above perform compilation at the statement level so if only one statement utilizes a temp table then that statement is the only one that gets recompiled. Contrary to popular belief, table variables can and do write to disk.

10 comments
Derek W 15 May 2014 at 11:43 am

Thank you Derek ( From Derek W)
My query time went from 25 mins to 1min 22secs ! Four years after posting you are still helping people!

Anonymous 14 May 2012 at 12:04 pm

Hey Derek this is one of your x-coWorkers from resMAE I can see your hair hasnt grown back out yet (j/k ) anyway “pal” is good to see you.

Ed Graham 18 Jan 2012 at 6:16 am

Fantastic tip, particularly after I read on StackOverflow that it couldn’t be done. My query involving an inner join on a table without a PK went from 3m 23s to 17s!

Julianne 23 Sep 2011 at 11:07 am

Great Derek! Thanks for the tip:)

Julianne 23 Sep 2011 at 11:06 am

Greate Derek!

Daniel N 24 Aug 2011 at 12:28 am

Awesome tip, my query batch went from 7 minutes down to 12 seconds.

Adam 25 Jul 2011 at 1:53 am

Blimy, this optimised my stored procedure. It took it down from 6 mins to 22 seconds. Thank you.

George 02 Apr 2010 at 4:58 am

Thanks Derek. A unique composite constraint helped drop the execution time of my sproc by half.

Derek D. 02 Apr 2010 at 9:10 am

Great George that’s awesome. Nice to see when these things help.

Twitted by SSISBI 23 Nov 2009 at 10:39 pm

[…] This post was Twitted by SSISBI […]

Featured Articles

 Site Author