TheSQLGuru (10/4/2012)
1) What is the point of this (isn't PRIMARY KEY sufficient by itself and the UNIQUE redundant?):PRIMARY KEY (ID),
UNIQUE (ID))
2) I can probably count on 2 hands the number of times in nearly 20 years of SQL server work where one or more indexes on a temp table have IMPROVED performance of a sproc overall. I cannot count the number of times I have REMOVED them in client's code to make things FASTER.
3) "The primary reason is that using a #temp table allows normal procedure compilation and caching, whereas table variables (@temp) may force a recompile every time a procedure is run." - incorrect on several points
4) " table variables do not update statistics" - nothing to do with updating stats, the limitation concerns whether or not they are created
5) "or the transaction log so they will fail if used in a TRANS/ROLLBACK TRANS scenario" - incorrect. Actually this leads to one of the few scenarios where they are beneficial, namely keeping data in place on rollback to subsequently use for another purpose such as auditing or debugging.
Working with big tables in DM environments, it's almost always worth putting an index or two on temp tables. Anything over 10k rows and it's likely to make a measurable difference. Less than 1k rows I never bother. It takes insignificant time to create an index and test.
6) Note that using an ORDER BY on an insert adds unnecessary overhead since SQL chooses the insert order internally and ignores the Order By even though the ordering is executed by the select statement.
SQL Server doesn't ignore the ORDER BY - but it will put those rows into the temp table however it sees fit, so to speak. If your running a few tens of rows or more into a temp table and you're going to cluster it, then test with and without ordering by the columns you're going to cluster. Measure the time taken for both statements (the INSERT and the CREATE CLUSTERED INDEX) and choose which works fastest. Using ORDER BY improves performance - quite a lot in some cases - sufficiently often to make this simple test worthwhile.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden