Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

Thanks!

share|improve this question
add comment

3 Answers

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

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
1  
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
add comment

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.