Sign up ×

My program lets people pick items from a list to help run a query. To return the minimum amount of data, I've had to use the IN clause.

I'm pretty sure there's a limit to how many items I can put in the IN clause of one SELECT statement, but I don't know exactly what the limit is. For my case, I need to know the limit for SQL server 2000 and SQL server 2008.


share|improve this question

3 Answers 3

up vote 5 down vote accepted

There isn't a quantity limit on the number of items in the IN clause. The only limit is that the entire batch cannot be more than the batch size maximum. In SQL 2008, this is 65,536 * Network Packet Size. In SQL 2000, I'm not sure if the limit was smaller than that.

This link describes these limits for 2008

share|improve this answer
The ORM I use passes the in clause items as parameters and executes the whole thing as a stored procedure. So, in my case, the number I wanted was the "Parameters per stored procedure" or 2100 –  IBC Feb 29 '12 at 22:48
I just phrased the question wrong, I guess. –  IBC Feb 29 '12 at 22:53

You're asking the wrong question. You're asking "what is my tolerance for pain when doing x?" when you should be asking "how can I reduce my pain by NOT doing x?"

For the SQL Server 2008 side, instead of passing a huge list of values into a single parameter, and then needing dynamic SQL or string parsing to actually treat it like a proper IN, why not use table-valued parameters? Here is an example.

For SQL Server 2000, you will need to use the old methods. Some workarounds to reduce the overall load include XML and bit-packing.

share|improve this answer
I'm using a Linq provider to generate the query for me. All queries have to go through the ORM (Nhibernate). I don't have direct DB access. –  IBC Feb 29 '12 at 22:38

Aeons ago, in SQL Server 7, there used to be a practical limit, see FIX: A SELECT Statement with a Large IN Clause May Fail with a Stack Overflow and the Client Disconnects. Now there is no limit, or more precisely you will hit other limits sooner.

But, as Aaron already points out, if you find yourself asking this question, you're doing it wrong.

share|improve this answer

Your Answer


