Is there any limit IN SQL?

499 Asked by DipikaAgarwal in SQL Server , Asked on Mar 14, 2023

Is there any limit for the content that the IN filter can handle? For example:

SELECT Name
FROM People
WHERE Job IN (All the values goes here)
Microsoft docs for IN says:

"Explicitly including an extremely large number of values (many thousands of values separated by commas) within the parentheses, in an IN clause can consume resources and return errors 8623 or 8632. To work around this problem, store the items in the IN list in a table, and use a SELECT subquery within an IN clause."

but is there any exact or approximate number for

many thousands of values

Answered by Ella Clarkson

Strictly speaking you're guaranteed for the query to fail with 65536 values. With that said, I think it's fairly safe to consider 32768 as an upper bound in practice, but it's not the least upper bound. The least upper bound depends on what else is going on in the query and other local factors. You can see this with a simple example. First put 100k rows into a heap:

DROP TABLE IF EXISTS dbo.Q228695;

CREATE TABLE dbo.Q228695 (ID BIGINT);

INSERT INTO dbo.Q228695 WITH (TABLOCK)

SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN

FROM master..spt_values t1

CROSS JOIN master..spt_values t2

OPTION (MAXDOP 1);

Suppose I want to put 32768 values in the IN clause for a query of the following form: SELECT COUNT(*) FROM dbo.Q228695 WHERE ID IN (); This is easy to do in SQL Server 2017 with STRING_AGG:

DECLARE @nou VARCHAR(MAX);

SELECT @nou = 'SELECT COUNT(*) FROM dbo.Q228695 WHERE ID IN (' + STRING_AGG(CAST(RN AS VARCHAR(MAX)), ',') + ')'

FROM

(

    SELECT TOP (32768) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN    FROM master..spt_values t1

    CROSS JOIN master..spt_values t2

) q

OPTION (MAXDOP 1);

EXEC (@nou);

I get the following error:

Msg 8632, Level 17, State 2, Line 1

Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

If I remove one value from the IN clause the query succeeds after 24 seconds. For this incredibly simple query, 32767 is the maximum number of values that still allows the query to execute. Note that Microsoft documentation on error 8632 says the following:

This issue occurs because limit in SQL Server limits the number of identifiers and constants that can be contained in a single expression of a query. This limit is 65,535.

32767 works and 32768 doesn't work. I don't think that it's a coincidence that 65535/2 = 32767.5. For whatever reason, the observed behaviour is that each constant in the IN clause counts as two towards the limit.

I do think that this is the wrong question to ask. If I put those same values into a temp table then the query executes in 0 seconds:

DROP TABLE IF EXISTS #t;

CREATE TABLE #t (ID BIGINT);

INSERT INTO #t WITH (TABLOCK)

SELECT TOP (32768) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN

FROM master..spt_values t1

CROSS JOIN master..spt_values t2

OPTION (MAXDOP 1);

SELECT COUNT(*)

FROM dbo.Q228695

WHERE ID IN (

    SELECT t.ID

    FROM #t t

);

Even if your query doesn't throw an error you're going to pay a heavy performance price once you put too many values into an IN clause.

Is there any limit IN SQL?

Your Answer