LutzM (11/13/2013)
But for the taks itself (taking aside the concatenation) your preferred solution is NOT EXISTS?Is this "usually" the fastest solution for a large table against a small lookup table or is this your preferred method to start with?
On non-nullable columns, NOT IN and NOT EXISTS are usually equivalent. When the columns are nullable, NOT IN performs terribly and can produce different results. EXISTS makes it easier to compare multiple columns without making the predicate non-SARGable
I also find EXISTS easier to read, but that's a personal thing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability