SQL WHERE ID IN (id1, id2, ..., idn)

Option 1 is the only good solution.

Why?

• Option 2 does the same but you repeat the column name lots of times; additionally the SQL engine doesn't immediately know that you want to check if the value is one of the values in a fixed list. However, a good SQL engine could optimize it to have equal performance like with IN. There's still the readability issue though...

• Option 3 is simply horrible performance-wise. It sends a query every loop and hammers the database with small queries. It also prevents it from using any optimizations for "value is one of those in a given list"

An alternative approach might be to use another table to contain id values. This other table can then be inner joined on your TABLE to constrain returned rows. This will have the major advantage that you won't need dynamic SQL (problematic at the best of times), and you won't have an infinitely long IN clause.

You would truncate this other table, insert your large number of rows, then perhaps create an index to aid the join performance. It would also let you detach the accumulation of these rows from the retrieval of data, perhaps giving you more options to tune performance.

Update: Although you could use a temporary table, I did not mean to imply that you must or even should. A permanent table used for temporary data is a common solution with merits beyond that described here.

What Ed Guiness suggested is really a performance booster , I had a query like this

select * from table where id in (id1,id2.........long list)

what i did :

DECLARE @temp table(            ID  int            )insert into @temp select * from dbo.fnSplitter('#idlist#')

Then inner joined the temp with main table :

select * from table inner join temp on temp.id = table.id

And performance improved drastically.