New answers tagged

8

This is somewhat subjective but I'm not at all a fan of SELECT ... INTO anyway and normally replace it with an explicit CREATE TABLE and INSERT ... SELECT as the datatypes, column names, and nullability can then be seen much more explicitly (and both can be minimally logged). In this case if you were to create the temp table with an unnamed primary key ...


5

The main factors in play here are: The optimizer does not try to find the best plan; its goal is to find a reasonable plan quickly It assumes the query will be run with a cold cache The cost model used favours sequential I/O over random I/O Repeated seeks into an index are assumed to be randomly distributed The cardinality estimate for a table variable ...



Top 50 recent answers are included