TheSQLGuru (3/12/2013)
One minor point ChrisM - never do a SELECT INTO... with an ORDER BY. No benefit there.
Thanks for the reminder, Kevin. I wouldn't say "never" however, I'd say "don't rely on it".
It can make a difference if you run a ton of rows into a temp table then cluster on the ORDER BY column - the clustered index can be created more quickly than without the ORDER BY. This may be an edge case in most shops but it's common in marketing and nowadays I always test both cases, with and without the ORDER BY, and pick whichever version is quickest. That means emulating as closely as possible the code block (query and cluster creation at least and often a few prior steps) as it would be run in prod to account for cacheing. Not always trivial but frequently worth the effort.
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