CTE to Select in Chunks?

I would like to use a SQL Server 2008 (recursive?) CTE to select 1 million rows at a time of a 400 million row table. The results will be written out to a text file, which I 开发者_开发技巧grok but not the CTE chunking part.

The table has good covering indexes on a DateTime column but no PK.

Does anyone have any suggestions?

Thanks.

You can create a psuedo PK by using the ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY <column>) as PK_Key windowing function but this will be suboptimal because the entire table will be ordered for each select.

A better way to do it is to chunk your statement by the indexes you have instead of on exactly 1m rows. That is, examine some of your data and pick an arbitrary limit which achieves what you want. If you have roughly 1m rows in a month, then for each data retrieval operation, get a months worth of data. You won't even need a CTE for it, as from the sounds of it you aren't dealing with recursive data structures.