How to reference one CTE twice?

Question

I have a very fat common table expression which includes row numbers so that I can return a paged result set. I also want to return the total number of records that match the query before I page the result set.

with recs as (select *, row_number() over (order by id) as rownum from ......)
select * from recs where rownum between @a and @b .... select count(*) from recs

Obviously my query above is patchy, but it's just for illustrating my point. I want a page of results AND the total number of matches. How do I do this without having to literally copy and paste the entire 20+ line CTE?

Answer 1

Don't think you can. From MSDN

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

Emphasis on "single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement."

This might be a situation where you want to use a Temporary Table.

CREATE TABLE #Recs
{
  .....
}
INSERT INTO #Recs
select *, row_number() over (order by id) as rownum from ......

If you don't know the structure of the table before hand you can use this form to create a temporary table:

select *, row_number() over (order by id) as rownum INTO #Recs from ......

You will be able to use the Temporary table in the manner you have described above.

Answer 2

A CTE can be used as many times as you like within the one query. But you could always make a view or inline TVF to reduce your typing, without needing to lose the performance benefit of paging with a temporary table.

Answer 3

You can use commas to create multiple CTEs that references the CTEs Above.

Just to illustrate what I mean:

with recs as (
select 
    *, 
    row_number() over (order by id) as rownum from ......
    ),
counts as (
    select count(*) as totalrows from recs
)
select recs.*,count.totalrows
from recs
cross apply counts 
where rownum between @a and @b .... 

This is not the a good solution.

The best solution I found to have the total count in a CTE without counting the records is described in this article.

DECLARE @startRow INT ; SET @startrow = 50
;WITH cols
AS
(
    SELECT table_name, column_name, 
        ROW_NUMBER() OVER(ORDER BY table_name, column_name) AS seq, 
        ROW_NUMBER() OVER(ORDER BY table_name DESC, column_name desc) AS totrows
    FROM [INFORMATION_SCHEMA].columns
)
SELECT table_name, column_name, totrows + seq -1 as TotRows
FROM cols
WHERE seq BETWEEN @startRow AND @startRow + 49
ORDERBY seq