regarding CTE's; very powerful tool for segmentation, but you do have to be careful as the engine will run the CTE query each time it's referenced. I would think, but haven't explicitly observed, that the db engine would somehow optimize this behavior, since all the reads are happening within the same transaction space it ought to be quite impossible to get a different result when running a CTE for multiple inclusion.

I've written some monsterous inline table valued functions that string 5 to 8 CTE's together, recalling the earlier CTE's in later segments; feeding prequalified data to work queries and paging CTEs and so on, only to find the query plan and overhead become monsterous. The showplan doesn't seem to indicate that there are any savings for multiple references to a CTE; question for Microsoft I suppose.

I eventually stepped back from the giant CTE approach and started using table valued variables; it's a different type of IO problem, but seemed to be a more effecient solution vs the monster CTE query.

When it comes to CTEs and the finding the best balance between convenience and performance you really have to try variations and compare results in the execution plan; as with so many sql server optimization topics; 'it depends'.

now, on to row numbering and paging; wanted to chip in my 2 cents worth.

One trick I've used to get a very tight grip on paging is to introduce a second ROW_NUMBER going in the opposite direction as the first, then summing the two to get a row count before page selection; it does introduce some additional overhead and it can be significant, but it the benefit outweighs the cost it can be quite useful. The version below uses a 0 based row and page indexes; first page is 0, first row is 0.

Note: removing the RN row number will significantly reduce overhead while continuing to allow you to use the page index functionality; you loose the row count and page count, but can still pull back a specific page in the sequence, accomplishing something like the LIMIT function mySql.

[font="Courier New"]

DECLARE @PageLimit INT, @PageIndex INT;

SELECT @PageLimit=20, @PageIndex=0;

SELECT

[RowIndex]=[IX]

,[RowCount]=[IX] + [RN]

,[PageCount]=CIELING(1.0 * [IX] + [RN] / @PageLimit)

,[PageIndex]=FLOOR(1.0 * [IX] / @PageLimit)

...

FROM (

SELECT

[IX]=ROW_NUMBER() OVER(ORDER BY [foo] ASC)-1

,[RN]=ROW_NUMBER() OVER(ORDER BY [foo] DESC)

...

) PageBase

WHERE FLOOR(1.0 * [IX] / @PageLimit) = @PageIndex;

[/font]