Create Index on Temp Table
-
Posted on November 23, 2009 by Derek Dieter
-
10
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.
- Comments (RSS)
- Trackback
- Permalink
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.
[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?
GA GUNA
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)?
The clearest example I’ve seen of this. Well done. I just finished implementing it and it worked like a champ.
Worked a treat. Was doing self joins with temp tables. Without the index it was 10 times slower….literally
great!
AT WHAT INSTANCE WE SHOULD USE INDEX. TELL ME A SCENARIO WHERE WE CAN USE CLUSTER INDEX
Great ………..!!!!!!!!!!!!!!!!!!
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.
The information is helpful…thanks
but where is the +1 button
Hi Derek,
How about creating index immediately after table declaration? Does it eliminate recompilation?
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.
