1

Please, what is the limit of items that can be in a list for an SQL query using a list for WHERE condition

SELECT field1, field2, field3 from Table WHERE id IN ('val1', 'val2', 'val3', ... 'valN')

What is the limit to N?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Austyns
  • 712
  • 2
  • 13
  • 21
  • 4
    Possible duplicate of ["IN" clause limitation in Sql Server](https://stackoverflow.com/questions/21178390/in-clause-limitation-in-sql-server) – forpas Mar 15 '19 at 10:42
  • 1
    ANSI SQL specifies no limit. SQL Server might have one. – jarlh Mar 15 '19 at 10:42
  • 1
    @AustineLyke . . . In SQL Server, probably more than you can type. I think it is limited to the length of the query string, which is over 1 Gbyte. (Do note that some databases do have such limits.) – Gordon Linoff Mar 15 '19 at 10:42
  • BTW, tables have _columns_, not fields. – jarlh Mar 15 '19 at 10:45
  • 1
    @GordonLinoff: because `IN` is internally expanded to a series of `OR` clauses, you typically hit the limits on what the query processor will accept as a parse tree before you hit the maximum text size of a query. – Jeroen Mostert Mar 15 '19 at 10:46
  • 1
    With a parameterized query, there's a limit of 2100 parameters per command. – Dan Guzman Mar 15 '19 at 10:46

2 Answers2

3

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.

Error 8623:

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

Error 8632:

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

microsoft docs

Austyns
  • 712
  • 2
  • 13
  • 21
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
2

Using IN for anything more than one or two values can be very expensive on resources. As Zaynul said a join is far more efficient.

The next step up from there for speed is to use the WHERE EXISTS functionality. The advantages are well documented, if you have a quick google around you'll find lots of examples and the reasons why its quicker. I won't repeat everyone elses comments here.

As an example have a look at this quick code:

DECLARE @SourceTable AS TABLE (ID INT NOT NULL, SomeText NVARCHAR(255));

DECLARE @AllowedTable AS TABLE (ID INT NOT NULL);

INSERT INTO @SourceTable
VALUES (1, 'Apples')
,      (2, 'Oranges')
,      (3, 'Bananas')
,      (4, 'Pears');

INSERT INTO @AllowedTable (ID)
VALUES (1)
,      (3);

SELECT  S.ID
,       S.SomeText
  FROM  @SourceTable S
 WHERE  EXISTS (SELECT  1 FROM  @AllowedTable A WHERE   A.ID = S.ID);

The where exists looks for the presence of a record rather than content of a record so limits reads, and you also don't end up with the problems of duplicate lines that can "appear from nowhere" when the data isn't quite what you expect.

Matthew Baker
  • 2,637
  • 4
  • 24
  • 49
  • 1
    `IN` and `EXISTS` get exactly the same execution plan in SQL Server (unlike `NOT IN` and `NOT EXISTS`). The execution plan for the following will be identical. `SELECT S.ID , S.SomeText FROM @SourceTable S WHERE S.ID in (SELECT A.ID FROM @AllowedTable A);` – Martin Smith Mar 15 '19 at 13:29