"IN" clause limitation

[karthik]

Does anybody know what is the limit for the number of values one can
have in a list of expressions (to test for a match) for the IN clause?

For example,
SELECT au_lname, state
FROM authors
WHERE state IN ('CA', 'IN', 'MD')

Is there a limit in the number of expressions that can be included in
the list to check for a match to STATE? I know this is a bad example as
there are only 50 states, but my actual query is similar to this, the
only difference being I may have about 1000 values in the 'IN' clause
to test for a match.

TIA!

[David Portas]

I don't think there is a documented limit and with 1000 values you'll
probably be ok. At some point you'll hit a memory allocation or batch
size limit if you keep adding values to the list.

Is that really the best way you can pass 1000 values? Why don't you put
the data in a table instead?

--
David Portas
SQL Server MVP
--

[karthik]

Thanks David! You were right on target - I've already hit the memory
allocation limit!

Table sounds good, but I was just tweaking somebody else's code. Thanks
for the idea though! :)

[Erland Sommarskog]

1000 values should work. However, the performance for large IN list
is absymal. The problem is not with query execution time, but query
compile time. So putting the values into a table is certainly a good idea.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp