How can I get a batch at a time of all the rows in a sql table?

Question

I have a table with 40 million rows.
I want to pick up about 2 million at a time and "process" them.
Why?
Cos processing processing 10million+ rows degrades performance, and often times out. (I need this to work independant of data size, so i cant just keep increasing the time out limit.)
Also, I'm using SQL Server.

Answer 1

Is there an increasing key, such as an identity key? And is it the clustered index? If so, it should be fairly simple to track the last key you got to, and do things like:

SELECT TOP 1000000 *
FROM [MyTable]
WHERE [Id] > @LastId
ORDER BY [Id]

Also - be sure to read it with something like ExecuteReader, so that you aren't buffering too many rows.

Of course, beyond a few thousand rows, you might as well just accept the occasional round-trip, and make a number of requests for (say) 10000 rows at a time. I don't think this would be any less efficient in real terms (a few milliseconds here and there).