Why Do I Get "The Server Encountered A Stack Overflow During Compile Time" Error Is SQL Server 2000 Sp4

I am trying to have around 6290 'AND' conditions in this query. I get the same for around 11945 'OR' conditions. Exception details: The server encountered a stack overflow during

Solution 1:

Try and optimise your AND/OR conditions.

SELECT * FROM foo
WHERE ([fooKey] = 1 AND Year = 1995)
OR ([fooKey] = 1 AND Year = 1996)
OR ([fooKey] = 1 AND Year = 1997)
OR ([fooKey] = 1 AND Year = 1998)
OR ([fooKey] = 1 AND Year = 1999)
OR ([fooKey] = 1 AND Year = 2000)
OR ([fooKey] = 1 AND Year = 2001)
OR ([fooKey] = 1 AND Year = 2002)
... ad infinitum

becomes

SELECT * FROM fooWHERE ([fooKey] = 1 AND Year between 1995 and 2002)
union
SELECT * FROM fooWHERE ([fooKey] = 10017 AND Year = 1995)
union
SELECT * FROM fooWHERE ([fooKey] = 10018 AND Year = 1997)
... slightly less

Or go to 64 bit and try and add enough memory for this not to happen...

Solution 2:

Interesting bug! The obvious question to ask you is, why? The stack overflow is because recursion is the typical way to parse an SQL statement like that, which builds up a syntax tree. Depending on what is pushed onto the stack with each recursive call, it's not surprising. Did it hurt the server? ;)