• ChrisM@Work (3/12/2013)


    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.

    Getting a bit off-topic here, but:

    A) I can probably count on two hands the number of times in 15 years of consulting on SQL Server I have seen cases where an index on a temp table is, overall, beneficial to the whole process.

    B) Speaking of "whole process", I am unclear how doing a SORT before inserting and then another (likely less costly but still work) SORT for a clustered index build could be more efficient than just doing one sort for building the CI. And again I will state that if you are hitting the table ONCE (which is the case in the VAST majority of times in my experience) then the index is almost certainly work for no benefit. :hehe:

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service