Friday, March 9, 2012

Indexes on temp tables in stored procs

I've learned that, to help prevent/minimize proc recompiles, it is best to create all needed temp tables first, before doing anything else in the sproc.

What about indexes on those temp tables? Is it better to create the index immediately after each temp table is created, or is it better to create all the temp tables first, then create all the indexes?

Since you don't have data in your temp tables at either point, it really doesn't matter that much. You'll have some metadata operations to do if you convert from a heap to a clustered index later on, but the cost will be miniscule, and virtually undetectable.

Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine

No comments:

Post a Comment