Thursday, April 14, 2011

Use Table Valued Parameters instead of IN clause (SQL Server Stack Overflow Issue)

Every SQL batch has to fit in the Batch Size Limit: 65,536 * Network Packet Size.
Your query is also limited by run time conditions. It will cause stack overflow because x IN (a,b,c) is nothing but x=a OR x=b OR x=c which creates an expression tree similar to x=a OR (x=b OR (x=c)), so it will become too deep for a large number of OR conditions. SQL 7 would hit a Stack Overflow at about 10k values in the IN, but now a days stacks are much deeper (because of x64), so it can go pretty deep.

Still if the IN clause is getting generated dynamically prefer Table Valued Parameters or use XML data type.