Rather than explicitly defining a clustered index as Tom did, it would be better to have a primary key in the table definition, since the separate index definition precludes temp-table caching. I assume that the reason Tom did not add an index, is because he had no knowledge whether this is a unique or not.
Generally, I recommend that when you define temp tables or table variables that you identify what are the keys, and then I mean the real keys and not some IDENTITY you slap on. This has two benefits:
1) Helps the optimizer and often gives better performance.
2) Serves to document your assumptions. And if the assumptions are wrong, the procedure will blow up rather than producing incorrect results.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
No comments:
Post a Comment