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.

13 comments
Michael 08 Jun 2017 at 10:35 am

Great, it is no different to creating a permanent table then.

I have been working from views which were built selects from view and joining to temp tables all without an index and realised that indexing would speed things up no end.

Some people have asked when creating an index would be appropriate and perhaps your table is to concise to illustrate this.

I would also wonder why you create an IDENTITY key field when surely the UserID column would be unique?

The two fields combined could be the Primary key with an index on each component to provide faster retrieval when a process only holds a partial key.

Mike 29 Mar 2017 at 2:07 pm

[quote]The downside is recompilation, however this can be suppressed with the use of (Keep Plan) or (Keep Fixed Plan) hints.[/quote]

Could you expand on this?
What recompilation does this cause?
What do those hints do, and where exactly should/could you be adding them?

JOSUA 21 Sep 2016 at 9:07 am
Sharon Kelly 14 Apr 2016 at 9:00 pm

This is a very clear example – thank you! But I have a question about WHEN to create an index. If your example table #Users was never used in anything but the FROM clause, with other things JOINed to it, and it never had a WHERE on it, would the index be useful?
For example:
select * from #Users
JOIN #anothertable at
on at.AppUserGUID = #users.UserGUID
In this case, isn’t it #anothertable that needs an index on its joining field AppUserGUID?
And that the index on #Users would be useless, if #users were never used in any other way than this (never a WHERE #Users.UserName or JOIN #Users on UserID = #othertable.UserID)?

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!
css.php