Tao Klerks (2/26/2010)
Hmm, I was more bothered by the fact that NOT IN() is assumed to NOT be SARGable... In SQL 2000 this was always true when there was more than one value in the NOT IN() parentheses, but as of SQL 2005 the optimizer does a pretty good job of using indexes even then!
At the place where I work we have a weekly Lunch and Learn sessions. About 6 months ago I was giving a presentation about indexes which included a dissection of the SARGable predicates based on the original article by Brad McGehee (the one which is included as a hyperlink in this QoD answer section). Well, when the NOT IN () was investigated when appied to one one the tables in one of our SQL Server 2005 databases, I could not demonstrate that NOT IN () is not SARGable because the execution plan had shown that the respective index was still used. Fortunately, I still had the old SQL Server 2000 database on one of my boxes at home, so I could later confirm the validity of the NOT IN() is not SARGable argument.
On the unrelated note, it looks like occasionally it pays off to come to work late, after Mr. Jones already edited the QoD :-). Since I am very familiar with Brad McGehee's article on SARGable predicates, I did not have any problems answering th QoD. Original typo aside, this is a very good question. Thank you Agrawal.
Oleg