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

There is no documented limit and I could not find a response that determines the exact size, so we can consider that there is no specific limit.

Certainly it is not unlimited, but the limit can be related by other things. One possibility is the batch size that is equivalent to 65536 network packets. Another can be the amount of function parameters. But I'm not sure if this applies. The size of any function is 2100 arguments, as per the documentation. There may be some more limit.

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

If you find a limit, in some situations you can still separate by more than IN and join with OR , if still possible.

Limit documentation

Using subquery in IN I reached the memory limit used by the bank, but not at the limit of the clause. I used the following script for this:

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 response of @bigown . In the test that I performed the query quietly reached% with% numbers compared.

EDIT

I also performed the following test to validate the element boundary and got the same result from 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;