Peter,

I love how you only return the totals in the first row of the output. this will definitely reduce the amount of data being returned to the application. I have a few things to note here about the functionality and you may have addressed them in your environment. First there should be some sort of filter in the first CTE. This way you can reduce the amount of data searched through. Secondly, have you looked at using a stored procedure to handle the code, instead of the application executing ad-hoc SQL? The reason I ask is, the db engine will never be able to reuse a query plan unless an exact binary match already exists. This means you are not benefiting from query plan reuse or parameterization. Also, by introducing a stored procedure solution, you can remove the nested cte and use variables to maintain the totals. You can then apply a case statement to the final result set to only put the totals on the first row. For example, case when @PgFirstRecord = RowNbr then @TotalPages else null end.