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