TheSQLGuru (8/31/2016)
ScottPletcher (8/31/2016)
TheSQLGuru (8/31/2016)
The optimizer can and often does (in my experience) introduce a SORT into the query plan on a small table to facilitate a merge join against a larger table.Quite true, which means you end up sorting the data anyway, and every time you run the query. Why not do it just once when you create the table and have it in place for the life of the table!?
I'm still (and always have been on this thread) talking about a temporary table.
Yes, after my initial misunderstanding, we both were, as was I above. No index on temp tables isn't bad, but far too often I've seen only a nonclus index. That almost never makes sense. Just cluster by that column instead.
I didn't think you objected to adding a nonclus index, on column3, to the main table, as I also suggested, but I could be wrong.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.