tags:

views:

825

answers:

4

I'm doing Select * From table Where Col IN (123,123,222,....)
If I put more than ~3000 numbers in the IN clause SQL throws and error.
Does anyone know if there's a size limit or anything?!!
Thank you.

+7  A: 

Depending on the database engine you are using, there can be limits on the length of an instruction.

SQL Server has a very large limit:

http://msdn.microsoft.com/en-us/library/ms143432.aspx

ORACLE has a very easy to reach limit on the other side.

For so large IN clauses, it's better to create a temp table, insert the values and do a JOIN. It works faster also.

tekBlues
not correct. Max size of SQL instruction or a batch is 65K * (network packet size which is usually 4K) = more then 250 Mb... http://msdn.microsoft.com/en-us/library/ms143432.aspx .
Bogdan_Ch
You are damned right on the spot, I complemented the answer. BTW, in ORACLE the limit is VERY easy to reach!.
tekBlues
A: 

There is a limit, but you can split your values into separate blocks of in()

Select * 
From table 
Where Col IN (123,123,222,....)
or Col IN (456,878,888,....)
Iain Hoult
+1  A: 

Why not do a where IN a sub-select...

Pre-query into a temp table or something...

create table SomeTempTable as select YourColumn from SomeTable Where UserPickedMultipleRecordsFromSomeListOrSomething

then...

select * from OtherTable where YourColumn in ( select YourColumn from SomeTempTable )

DRapp
Better to do a join to the temp table than a subselect usually.
HLGEM
+3  A: 

Depending on your version, use a table valued parameter in 2008, or some approach described here:

Arrays and Lists in SQL Server 2005

AlexKuznetsov