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

Question

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 compile time. at System.Data. lClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException excepti , Boolean breakConnection)

Answer 1

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? ;)

Answer 2

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...