Could there any advantage in replacing an IN clause with a join to a table variable?
e.g. instead of using
And add an index on Col1;
(might SQL Server possibly do something like this under the covers anyway?)
asked Mar 08 '12 at 10:31 AM in Default
Depending on the numbers of rows involved in the tables being joined then yes, using a table variable, or indeed a temporary table, can have huge performance benefits over using WHERE .. IN (...). With careful thought on indexing then this can further ensure scalability performance.
I would certainly test all three options and allow for potential growth in data and how that might affect the number of options in the IN(..).
Personally I'd expect/prefer to use the JOIN method where there are more than a 3 or 4 values, purely from experience, 3 or 4 turns into 20 or 30 in a pretty short space of time