Maximum Size For a SQL Server Query? in Clause? Is There a Better Approach

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

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;

IN clause limitation in Sql Server

Yes, there is a limit, but Microsoft only specifies that it lies "in the thousands":

Explicitly including an extremely large number of values (many thousands of values separated by commas) within the parentheses, in an IN clause can consume resources and return errors 8623 or 8632. To work around this problem, store the items in the IN list in a table, and use a SELECT subquery within an IN clause.

Looking at those errors in details, we see that this limit is not specific to IN but applies to query complexity in general:

Error 8623:

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

Error 8632:

Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

Limit on the WHERE col IN (...) condition

Depending on the database engine you are using, there can be limits on the length of an instruction.

SQL Server has a very large limit:

http://msdn.microsoft.com/en-us/library/ms143432.aspx

ORACLE has a very easy to reach limit on the other side.

So, for large IN clauses, it's better to create a temp table, insert the values and do a JOIN. It works faster also.

Is there a limit to how long a SQL query can be in SQL Server?

I never encountered a problem of SQL query being too long in terms of number of characters, but there is a maximum number of tables a query can reference (256) and I hit this limitation a few times.

Have you ever encountered a query that SQL Server could not execute because it referenced too many tables?

UPDATE

In recent versions of SQL Server, the number of tables per SELECT statement is limited only by available resources.

Maximum values possible in a WHERE IN query

At the end my solution which works not so bad:
1. Sorted the ID's (to save server paging)
2. Created with C# code query's with 500 ID's in them.
3. sent the query's one by one.

I assume that when i worked with query having 1000+ ids the sql server time to process the query was slowing me down (after all any query you run in sql server is being process and optimized).

I Hope this help someone

Is there a maximum size for the array after the T-SQL IN operator?

You really don't want to do that. It would be better to dump those values into an indexed table and use IN as a subquery (which typically implements a SEMI JOIN (more info) vs the array of strings (which is typically implement as a series of OR operations.

from BOL:

Including an extremely large number of values (many thousands) in an
IN clause can consume resources and return errors 8623 or 8632. To
work around this problem, store the items in the IN list in a table.

Error 8623:

The query processor ran out of internal resources and could not
produce a query plan. This is a rare event and only expected for
extremely complex queries or queries that reference a very large
number of tables or partitions. Please simplify the query. If you
believe you have received this message in error, contact Customer
Support Services for more information.

Error 8632:

Internal error: An expression services limit has been reached. Please
look for potentially complex expressions in your query, and try to
simplify them.

Limit to number of Items in list for WHERE clause SQL query

Explicitly including an extremely large number of values (many thousands of values separated by commas) within the parentheses, in an IN clause can consume resources and return errors 8623 or 8632. To work around this problem, store the items in the IN list in a table, and use a SELECT subquery within an IN clause.

Error 8623:

The query processor ran out of internal resources and could not
produce a query plan. This is a rare event and only expected for
extremely complex queries or queries that reference a very large
number of tables or partitions. Please simplify the query. If you
believe you have received this message in error, contact Customer
Support Services for more information.

Error 8632:

Internal error: An expression services limit has been reached. Please
look for potentially complex expressions in your query, and try to
simplify them.

microsoft docs

What is maximum number of elements in in keyword for the query having Where

If you need to pass so many elements that this becomes a problem, you should consider a stored procedure with a used defined table valued parameter as a parameter (which should contain your list).

edit: see http://blogs.msdn.com/b/felixmar/archive/2010/10/27/how-to-create-and-execute-a-stored-procedure-using-a-table-as-a-parameter.aspx

---

Related Topics