SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

AskSQLTeam
Ask SQLTeam Question

USA
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.

richmondata
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

Canada
1 Posts

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

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

crokusek
Starting Member

USA
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.

http://dba.stackexchange.com/questions/20195/why-are-runtimes-different-for-a-table-including-a-primary-key-versus-a-table-wi
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000