471,049 Members | 1,406 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,049 software developers and data experts.

"IN" clause limitation

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!

Jul 23 '05 #1
3 41616
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
--

Jul 23 '05 #2

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! :)

David Portas wrote:
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
--


Jul 23 '05 #3
karthik (ka***********@gmail.com) writes:
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.


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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by BerkshireGuy | last post: by
43 posts views Thread by markryde | last post: by
2 posts views Thread by John Smith | last post: by
3 posts views Thread by Kevin Chambers | last post: by
4 posts views Thread by assgar | last post: by
15 posts views Thread by David C. Ullrich | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.