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"
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.
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.
Article: Optimizing Performance / Indexes on Temp Tables
Posted - 01/07/2004 : 12:39:16
