SQL Server 2000 gives error on queries that contain more than about 12500 conditions

Question

Hi

I have problem with SQL Server 2000 and queries that contain a large number of conditions. When these queries are sent to the SQL Server I get one of the following error messages depending on the query (error messages edited for readability):

[Microsoft][ODBC SQL Server Driver][SQL Server]
SqlDumpExceptionHandler: 
Process 61 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. 
SQL Server is terminating this process. (SQL-HY000)

or

[Microsoft][ODBC SQL Server Driver][SQL Server]
The server encountered a stack overflow during compile time. (SQL-42000)

The limit seems to be about 12500 conditions.

1. Have anyone else experienced the same type of problems?
2. Are there any settings in SQL Server 2000 that can be tweaked to allow more conditions in a query?
3. Does SQL Server 2005 handle more conditions in a query that SQL Server 2000?

--- Edit

The queries in question are typically on the following form

SELECT
...
FROM 
    TABLE
WHERE
    -- the rows I want
    ( ( COLUMN2 = a AND COLUMN3 = b ) OR ( ( COLUMN2 = c AND COLUMN3 = d ) ) 
    AND
    COLUMN1 NOT IN ( ... ) -- The rows that I don't want

The combinations for COLUMN2 and COLUMN3 can be somewhere between 20000 and 60000 I think, though the exact number is not known at this time.

The values for COLUMN2 and COLUMN3 does not follow a pattern, so it is not possible to simplify the query enough so that the problem is avoided altogether.

This values in the IN expression can also reach be about the same number.

--- Edit 2

Solution:

This was the solution I ended up with. As least until any new problems crop up.

1. First the query is split into several queries. Together all the queries cover the rows to select. This avoids the condition limit in SQL Server and also seems to greatly improve efficiency in the cases where the limit wasn't reached as well.
2. Then result from each query is joined using UNION.
3. The total result is then filtered by the application instead of the SQL Server. This seems to be reasonably efficient in this case. If at a later time this proves to be to inefficient, this can probably be replaced by the solution outline in the answer below.

Answer 1

Why do I get “The server encountered a stack overflow during compile time” error is SQL Server 2000 Sp4

Answer 2

If you really need thousands of conditions on a query, and can't simplify it using ranges you may try joining in a temp table. Create a temp table with the values in it and then INNER JOIN to this in your query, which will filter on those values in the table.

from something like (only speculation, OP has provided no example code):

SELECT
    *
    FROM MyTale
    WHERE (a=5 AND b='apple' AND c=1024)
        OR (a=3 AND b='pear' AND c=2048)
        OR (a=8 AND b='apple' AND c=2048)
        ....

to:

CREATE TABLE #TempJoin
(a int
,b char(10)
,c int
)

INSERT INTO #TempJoin VALUES (5,'apple',1024)
INSERT INTO #TempJoin VALUES (3,'pear',2048)
INSERT INTO #TempJoin VALUES (8,'apple',2048)
....

SELECT
    m.*
    FROM MyTale              m 
        INNER JOIN #TempJoin t ON m.a=t.a AND m.b=t.b AND m.c=t.c

EDIT based on OP's edit...
I'm still very perplexed with "why" so many conditions. I just can't imagine why, could you give a little background on the nature of the problem you are trying to solve?

Where are all all of the values used in the conditions coming from? user input, a file, another database table?

There may be a very simple solution that involves doing things differently, which results in not needing to have thousands of conditions in your query.

The only way to solve your problem (as presented) is to store your conditions as rows in two tables: RowsToKeep and RowsToRemove. You can use regular tables, #temp tables, or ##globalTemp tables. You don't give enough info to recommend which to use (how often does this query run? will there be multiple similar queries running at the same time, etc..) In my example code, I'll use a regular table, and assume that nothing will interfere with the regular tables (no one else will try to use/modify RowsToKeep and RowsToRemove while this query is being prepared or being run).

CREATE TABLE RowsToKeep
(a int       --you don't say the data type, but you can make these anything
,b char(10)  --you don't say the data type, but you can make these anything
)

CREATE TABLE RowsToRemove
(c int       --you don't say the data type, but you can make these anything
)

For a query such as you describe, I'd guess that you are building it dynamically using loops. TRUNCATE or DELETE the two tables first, then as you loop INSERT INTO these tables instead of building a dynamic query conditions. After inserting all the values, You can then use the following simple query, which will function the same as your original, but not be limited by number of conditions:

SELECT
...
    FROM TABLE                        t
        INNER JOIN RowsToKeep         k ON t.Column2=k.a AND t.Column3=k.b
        LEFT OUTER JOIN RowsToRemove  r ON t.Column1=r.a
    WHERE r.a IS NULL