Maximum size for a SQL Server Query? IN clause? Is there a Better Approach [duplicate]

by

Every SQL batch has to fit in the Batch Size Limit: 65,536 * Network Packet Size.

Other than that, your query is limited by runtime conditions. It will usually run out of stack size 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 gets very deep with a large number of OR. SQL 7 would hit a SO at about 10k values in the IN, but nowdays stacks are much deeper (because of x64), so it can go pretty deep.

Update

You already found Erland’s article on the topic of passing lists/arrays to SQL Server. With SQL 2008 you also have Table Valued Parameters which allow you to pass an entire DataTable as a single table type parameter and join on it.

XML and XPath is another viable solution:

SELECT ...
FROM Table
JOIN (
   SELECT x.value(N'.',N'uniqueidentifier') as guid
   FROM @values.nodes(N'/guids/guid') t(x)) as guids
 ON Table.guid = guids.guid;

More Related Contents:

  1. Convert text to system date format [duplicate]
  2. How to get a particular preceding value of a column in SQL [closed]
  3. SQL where clause odd behavior [closed]
  4. How do I obtain a Query Execution Plan in SQL Server?
  5. What is the meaning of the prefix N in T-SQL statements and when should I use it?
  6. Selecting data from two different servers in SQL Server
  7. How important is the order of columns in indexes?
  8. Remove Identity from a column in a table
  9. How to split a single column values to multiple column values?
  10. Why no windowed functions in where clauses?
  11. Formatting Numbers by padding with leading zeros in SQL Server
  12. T-SQL Cast versus Convert
  13. What is a stored procedure?
  14. T-SQL datetime rounded to nearest minute and nearest hours with using functions
  15. convert Excel Date Serial Number to Regular Date
  16. For Nvarchar(Max) I am only getting 4000 characters in TSQL?
  17. Partition Function COUNT() OVER possible using DISTINCT
  18. Count based on condition in SQL Server
  19. Alter user defined type in SQL Server
  20. Best equivalent for IsInteger in SQL Server
  21. Store multiple bit values in a single table column
  22. Syntax of for-loop in SQL Server
  23. SQL Server equivalent to MySQL enum data type?
  24. How to correctly insert newline in nvarchar [duplicate]
  25. How do you copy a record in a SQL table but swap out the unique id of the new row?
  26. Using IF ELSE statement based on Count to execute different Insert statements
  27. SQL – How do I get only the numbers after the decimal?
  28. How to retrieve records for last 30 minutes in MS SQL?
  29. Group consecutive rows of same value using time spans
  30. Why do SQL Server Scalar-valued functions get slower?

Leave a Comment