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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Optimizing Performance / Indexes on Temp Tables
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ask SQLTeam Question

0 Posts

Posted - 01/07/2004 :  12:39:16  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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 - 01/07/2004 :  13:59:53  Show Profile  Reply with Quote
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.

Edited by - richmondata on 01/07/2004 14:00:43
Go to Top of Page

Marios Philippopoulos
Starting Member

1 Posts

Posted - 06/18/2009 :  10:40:48  Show Profile  Reply with Quote

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

Starting Member

1 Posts

Posted - 06/30/2012 :  03:12:13  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000