What is the argument limit of the IN operator in SQL Server?

Question

In Oracle are 1000 arguments, which limit in SQL Server?

Answer 1

There is no set limit in documentation and I could not find an answer that determines the exact size, so we can consider that there is no specific limit.

It is certainly not unlimited, but the limit may be related by other things. One possibility is the size of the batch which is equivalent to 65536 network packets. Another may be the number of function parameters. But I’m not sure if this applies. The size of any function is 2100 arguments, according to the documentation. It is possible that another limit exists.

So unlike Oracle, SQL Server does not have this specific limitation.

If you find a limit, in some situations you can still separate into more than one IN and unite with a OR, if it is still possible.

Documentation on limits

Answer 2

Using a subquery in the IN I reached the memory limit used by the bank, but not within the clause. I used the following script for that reason:

declare @numeros table(numero int);
declare @proximo int = 1;

set nocount on;

while @proximo < 100000
begin
  insert into @numeros values(@proximo);

  print @proximo;

  if @proximo % 100 = 0
  begin
    select 1
      where 1000000 in (select numero
                          from @numeros);
  end;

  set @proximo = @proximo + 1;
end;

Probably declaring the elements the limit is the one reported in reply of @Maniero. In the test I performed query arrived quietly to 999999 compared numbers.

---

EDIT

I also performed the following test to validate the element limit and obtained the same result as the previous test:

declare @proximo int = 1,
        @in      varchar(max);

set nocount on;

while @proximo < 100000
begin
  set @in = isnull(@in + ',', '') + cast(@proximo as varchar);

  print @proximo;

  if @proximo % 100 = 0
  begin
    exec('select 1 where 1000000 in (' + @in + ')');
  end;

  set @proximo = @proximo + 1;
end;