SQL Server Error 8632 – Handling Over 100,000 Entries in WHERE Clause

configurationlimitssql server

My problem (or at least the error message) is very similar to query processor ran out of internal resources – extremely long sql query.

My customer is working with an SQL select-query, containing a where-clause with exactly 100,000 entries.

The query is failing with error 8632 and error message

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

I find it very peculiar that this error message is thrown, exactly at 100,000 entries, so I wonder if this is a configurable value. Is this the case and in case yes, how can I increase this value to a higher one?

On MSDN, there is the proposal to re-rewrite the query, but I'd like to avoid this.

Meanwhile I've found out that the list of entries I'm talking about contains natural numbers, quite some of them seem to be sequential (something like (1,2,3,6,7,8,9,10,12,13,15,16,17,18,19,20).

This makes the SQL where-clause something like:

where entry in (1,2,3,6,7,8,9,10,12,13,15,16,17,18,19,20)

I could transform this into:

where (entry between 1 and 3) OR
      (entry between 6 and 10) OR
      (entry between 12 and 13) OR
      (entry between 15 and 20)

Can this be shortened by:

where entry in (1,...,3,6,...,10,12,13,15,...,20)

…or something similar? (I know it's a long shot, but it would make software updates easier and more readable)

For your information: the data in the where-clause are the result of a calculation, done on another table: first the entries of that table are read and filtered at the beginning, then some extra processing is done (which is impossible to do using SQL), the result of that extra processing is more filtering and the result of that is used in the where-clause. As it was impossible to write the complete filtering in SQL, the mentioned method has been used. Obviously the content of the where-clause might change at every processing, hence the need of a dynamic solution.

Best Answer

To search for more than 100,000 values, put them in a temp table instead, one row per value that you're searching for. Then, join your query to that temp table for filtering.

Something with more than 100,000 values isn't a parameter - it's a table. Rather than thinking about raising the limit, consider Swart's Ten Percent Rule: if you're approaching 10% of a SQL Server limit, you're probably going to have a bad time.

Related Question