"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!

Jul 23 '05 #1

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
--

Jul 23 '05 #2

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

David Portas wrote:[color=blue]
> 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[/color]
put[color=blue]
> the data in a table instead?
>
> --
> David Portas
> SQL Server MVP
> --[/color]

Jul 23 '05 #3

Erland Sommarskog

karthik (karthiksmiles@gmail.com) writes:[color=blue]
> 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.[/color]

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

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

Jul 23 '05 #4