Article ID: 288095 - Last Review: November 2, 2007 - Revision: 4.4

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

View products that this article applies to.

System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.

This article was previously published under Q288095

BUG #: 235727 (shiloh_bugs)
BUG #: 58274 (sqlbug_70)

Expand all | Collapse all

SYMPTOMS

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.

Back to the top

WORKAROUND

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)

Back to the top