Posted by Erland Sommarskog on 01/18/07 23:04

Bob Barrows [MVP] (reb01501@NOyahoo.SPAMcom) writes:
> Now you've got me really intrigued. Based on everything I've ever read,
> IN comparisons are supposed to be non-sargable, and therefore
> non-optimizable. Someone from the SQL Server groups please correct me if
> I am wrong.

coi IN (val1, val2, ...)

is just a shortcut for

col1 = val1 OR col2 = val2 OR ...

and it's perfectly possible for the optimizer to work with IN to produce a
good plan. The main problem is that for many values, the optimization time
can exceed the real execution time by far.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Row triggers equivalent in sql server 2000
• Prev in forum: MI5 Persecution: Hotchkies FAQ (1036)
• Thread view: Re: Bizarre slow query problem (again)

[Reply to this message]