4

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

  • I had an issue last year where the in clause had more than 30,000 values, it was a query generated dinamically from the application. But seems to be an issue not with the values but something else, as later we hit the error with more than 20,000 values, we ended asking the developers to use the select subquery instead of using all the values. – dbamex Feb 1 at 15:46
  • I though the select query would be translated to the actual values separated by commas. but I think I´m wrong, probably it does some other "behind scenes" stuff to optimized that. – Unnamed Feb 1 at 15:49
  • 1
    Subquery will translate to a join typically, not the materialization of a set of comma-separated values. – Aaron Bertrand Feb 1 at 15:53
10

It depends. Seriously. That’s why the docs can’t be specific about where you will notice degradation in your environment.

The solution is to just stop doing this (and worrying about it) and use a table-valued parameter.

If you have the values in C# in such a way that you can build a comma-separated list as a string to concatenate together in a query, you have the values in C# in such a way that you could stuff them in a DataTable or other structure (maybe that's where they're coming from in the first place), and pass that structure as a parameter to the stored procedure.

  • based on what it depends?, maybe the field type? I´ve read that Joining the tables is better (and that is what I usually do), but also I´ve seen some queries where the only possible fix is the IN filter. Maybe break apart the parameters?. – Unnamed Feb 1 at 15:46
  • 3
    Data type, size of actual values, size of overall text, network speed, concurrency, ... what do you mean by “the only possible fix” - fixing what exactly? – Aaron Bertrand Feb 1 at 15:54
  • 3
    If you have the values in C#, you can put them into a DataTable, and pass that as a parameter to the stored procedure. – Aaron Bertrand Feb 1 at 16:38
  • 3
    @Joshua I feel like if you're hitting that kind of limit, there is probably a better way. A user isn't picking 30,000 individual items, are they picking an entire category, or all of the subordinates of a manager, or all of the parts in a manufacturing process, or just picking "select all" on a massive drop-down? Or everything except a handful of items? Seems like you could handle those cases differently instead of throwing 30,000 items at the database in any form. – Aaron Bertrand Feb 1 at 17:53
  • 1
    @Joshua Well, you can create a TVP with a (clustered or not) primary key or unique constraint, so you don't have to rely on the default behavior, might be worth testing it out instead of dealing with branching logic or temp tables sometimes... – Aaron Bertrand Feb 1 at 18:16
5

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.