Stack overflow occurs when you run a query that contains a large number of arguments inside an IN or a NOT IN clause in SQL Server

Article translations Article translations
Article ID: 288095 - View products that this article applies to.
This article was previously published under Q288095
BUG #: 235727 (shiloh_bugs)
BUG #: 58274 (sqlbug_70)
Expand all | Collapse all


Queries that contain a large number of arguments (thousands) inside an IN or a NOT IN clause may generate a stack overflow. For example, the following query results in a stack overflow:
SELECT max(au_id) FROM authors WHERE au_id IN(1,2,5,......,11571)  
-- Query contains over 11570 arguments.
The SQL Server error log contains information similar to the following when the stack overflow occurs:
2000-08-10 12:02:37.87 spid51    08/10/00 12:02:37 Stack Overflow Dump not possible - Exception c00000fd E at 0x00587286
2000-08-10 12:02:37.87 spid51    Address=587286 Exception Code = c00000fd
2000-08-10 12:02:37.87 spid51    eax=195922d0 ebx=19592338 ecx=2ad0e938 edx=00000007
2000-08-10 12:02:37.87 spid51    esi=196ce2c8 edi=19592180 eip=00587286 esp=2ac93000
2000-08-10 12:02:37.87 spid51    ebp=2ac93028 efl=00010202
2000-08-10 12:02:37.87 spid51    cs=1b ss=23 ds=23 es=23 fs=38 gs=0
2000-08-10 12:02:37.87 spid51    1: Return Address 00587286
2000-08-10 12:02:37.87 spid51    2: Return Address 0058728B.................
In some cases, SQL Server may actually shutdown as a result of the stack overflow.


Rewrite the query and use a #temp table to contain the values in the IN list instead of using an IN clause. For example, the preceding query can be re-written like this:
CREATE TABLE #IN_values (au_id char(4))
INSERT INTO #IN_values select au_id FROM Table_with_values_123456789...

SELECT max(au_id)
FROM authors as A
JOIN #IN_values as I ON (A.au_id = I.au_id)


Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.


The client is not disconnected but this error message may occur:
Server: Msg 8621, Level 17, State 1, Line 2 Internal Query Processor Error: The query processor ran out of stack space during query optimization.


Article ID: 288095 - Last Review: November 2, 2007 - Revision: 4.4
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Desktop Engine (Windows)
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Standard Edition
kbprb kbpending KB288095

Give Feedback


Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from