I do not have the code handy at present, and am too sick right now to recreate it :-)
But in my case I was filtering rows out based on a primary key, so I was using except instead of:
- Select ... from tab1 where tab1.key not in (select key from tab2)
- Select ... from tab1 left outer join tab2 on tab1.key = tab2.key where tab2.key is null
It was faster than both on my test system, using a dbcc freeproccache, dbcc dropcleanbuffers and checking the total cost of the queries.