Maximum values possible in a WHERE IN query

I have a table with over 3000000 entries, and i need to delete 500000 of them with given ID's. My idea is to create a query like:

DELETE FROM TableName WHERE ID IN (id1, id2, ...........)

开发者_JS百科

which I generate with a simple C# code. The question is: is there a limit to how many values I can set in the array of ID's.

And if someone have a better way to achieve this delete more efficiently I'm open to ideas.

---

If your IDs can't be determined with whatever comparison (as in WHERE ID < 1000000) you could

1. INSERT them into a temp table with multiple inserts and then
2. JOIN this temp table to yours

But inserts may become problematic. You should check that. How could you speed this thing up?

1. make deletes in several bulks
2. insert IDs into temp table in bulks

---

At the end my solution which works not so bad: 1. Sorted the ID's (to save server paging) 2. Created with C# code query's with 500 ID's in them. 3. sent the query's one by one.

I assume that when i worked with query having 1000+ ids the sql server time to process the query was slowing me down (after all any query you run in sql server is being process and optimized).

I Hope this help someone