“IN” clause limitation in Sql Server

by

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.

More Related Contents:

  1. SQL where clause odd behavior [closed]
  2. SQL Server Exec issue [closed]
  3. How do I escape a single quote in SQL Server?
  4. NOT IN vs NOT EXISTS
  5. Simple way to transpose columns and rows in SQL?
  6. Only inserting a row if it’s not already there
  7. Unpivot with column name
  8. Selecting data from two different servers in SQL Server
  9. How can I delete using INNER JOIN with SQL Server?
  10. How to convert DateTime to VarChar
  11. Most efficient way in SQL Server to get date from date+time?
  12. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  13. SQL performance on LEFT OUTER JOIN vs NOT EXISTS
  14. Stored Procedure with optional “WHERE” parameters
  15. SQL Server: Howto get foreign key reference from information_schema?
  16. SCOPE_IDENTITY() for GUIDs?
  17. How do I perform a GROUP BY on an aliased column in SQL Server?
  18. Need to list all triggers in SQL Server database with table name and table’s schema
  19. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  20. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  21. Get previous and next row from rows selected with (WHERE) conditions
  22. Best equivalent for IsInteger in SQL Server
  23. How to list files inside a folder with SQL Server
  24. Can SQL Server Express LocalDB be connected to remotely?
  25. SQL Server SELECT INTO @variable?
  26. Select a column if other column is null
  27. How to subtract 30 days from the current date using SQL Server
  28. Add a row number to result set of a SQL query
  29. Use one CTE many times
  30. How to do a case sensitive GROUP BY?

Leave a Comment