Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Article: Optimizing Performance / Indexes on Temp Tables

Author  Topic 

Ask SQLTeam Question

0 Posts

Posted - 2004-01-07 : 12:39:16
This article comes to us from Bill Richmond. Bill writes "The general rule is to avoid using temp tables, usually in favor of derived tables or table variables, but there are times when it seems that nothing else will do the job. Or, maybe we just inherit complex code that already makes extensive use of temp tables. Frequently, query performance against large temp tables can benefit from adding a few well-chosen indexes"

Article Link.

Starting Member

3 Posts

Posted - 2004-01-07 : 13:59:53
Note from the author.
Much to my regret, when executed, the short code examples included in the article don't actually have the problem I'm trying to address. However, I have recently had to use this technique to solve this very problem in a production SQL 2000 Enterprise Edition / sp3 environment, so I think this hint should still be relevant.
Go to Top of Page

Marios Philippopoulos
Starting Member

1 Post

Posted - 2009-06-18 : 10:40:48

Thanks for the very informative article. Does this apply as well to SQL Server 2005/2008?
Go to Top of Page

Starting Member

1 Post

Posted - 2012-06-30 : 03:12:13
The optimizer in SQL Server 2008 Express seems to favor a primary key defined within the temp table. I have a case where it ignores a unique clustered index created after the temp table had been populated.
Go to Top of Page

- Advertisement -