How do I get rowcount of a cte in a separate dataset?

Question

I have identified a way to get fast paged results from the database using CTEs and the Row_Number function, as follows...

DECLARE @PageSize INT = 1
DECLARE @PageNumber INT = 2

DECLARE @Customer TABLE (
  ID       INT IDENTITY(1, 1),
  Name     VARCHAR(10),
  age      INT,
  employed BIT)

INSERT INTO @Customer
    (name,age,employed)
SELECT 'bob',21,1
    UNION ALL
SELECT 'fred',33,1
    UNION ALL
SELECT 'joe',29,1
    UNION ALL
SELECT 'sam',16,1
    UNION ALL
SELECT 'arthur',17,0;


WITH cteCustomers
     AS ( SELECT
            id,
            Row_Number( ) OVER(ORDER BY Age DESC) AS Row
          FROM   @Customer
          WHERE  employed = 1 
     /*Imagine I've joined to loads more tables with a really complex where clause*/
     )       

SELECT
  name,
  age,
  Total = ( SELECT
              Count( id )
            FROM   cteCustomers )
FROM       cteCustomers
INNER JOIN @Customer cust
  /*This is where I choose the columns I want to read, it returns really fast!*/
  ON cust.id = cteCustomers.id
WHERE      row BETWEEN ( @PageSize * @PageNumber - 1 ) AND ( @PageSize * ( @PageNumber ) )
ORDER      BY row ASC

Using this technique the returned results is really really fast even on complex joins and filters.

To perform paging I need to know the Total Rows returned by the full CTE. I have "Bodged" this by putting a column that holds it

Total = ( SELECT
              Count( id )
            FROM   cteCustomers )

Is there a better way to return the total in a different result set without bodging it into a column? Because it's a CTE I can't seem to get it into a second result set.

score 6 · Accepted Answer · edited May 23 '17 at 11:44

Without using a temp table first, I'd use a CROSS JOIN to reduce the risk of row by row evaluation on the COUNT

To get total row, this needs to happen separately to the WHERE

WITH cteCustomers
     AS ( SELECT
            id,
            Row_Number( ) OVER(ORDER BY Age DESC) AS Row
          FROM   @Customer
          WHERE  employed = 1 
     /*Imagine I've joined to loads more tables with a really complex where clause*/
     )       

SELECT
  name,
  age,
  Total
FROM       cteCustomers
INNER JOIN @Customer cust
  /*This is where I choose the columns I want to read, it returns really fast!*/
  ON cust.id = cteCustomers.id

CROSS JOIN

(SELECT Count( *) AS Total FROM   cteCustomers ) foo

WHERE      row BETWEEN ( @PageSize * @PageNumber - 1 ) AND ( @PageSize * ( @PageNumber ) )
ORDER      BY row ASC

However, this isn't guaranteed to give accurate results as demonstrated here:
can I get count() and rows from one sql query in sql server?

Edit: after a few comments.

How to avoid a CROSS JOIN

WITH cteCustomers
     AS ( SELECT
            id,
            Row_Number( ) OVER(ORDER BY Age DESC) AS Row,
            COUNT(*) OVER () AS Total --the magic for this edit
          FROM   @Customer
          WHERE  employed = 1 
     /*Imagine I've joined to loads more tables with a really complex where clause*/
     )       

SELECT
  name,
  age,
  Total
FROM       cteCustomers
INNER JOIN @Customer cust
  /*This is where I choose the columns I want to read, it returns really fast!*/
  ON cust.id = cteCustomers.id
WHERE      row BETWEEN ( @PageSize * @PageNumber - 1 ) AND ( @PageSize * ( @PageNumber ) )
ORDER      BY row ASC

Note: YMMV for performance depending on 2005 or 2008, Service pack etc

Edit 2:

SQL Server Central shows another technique where you have reverse ROW_NUMBER. Looks useful

@digiguru: See my link to the other question please for a working example — gbn, May 24 '11 at 15:38
@gbn: It seems like your other example is accurate. There is no-one disputing the accuracy of the result set. — digiguru, May 24 '11 at 15:46
@digiguru: My answer is actually wrong there compared to Chris Bednarski's answer. He demonstrates that a CROSS JOIN can give inaccurate results under high load. The correct way (Chris B's way) is COUNT..OVER but.. ah ha.. we can work it into your query... will update — gbn, May 24 '11 at 15:55
Hey check this out: http://www.sqlservercentral.com/articles/T-SQL/66030/ allows an even faster technique — digiguru, May 24 '11 at 15:59
@digiguru - Paul White looked at this method and concluded ["This method is the slowest overall, with high CPU usage, a large memory grant, and the largest number of physical reads."](http://www.sqlservercentral.com/articles/paging/70120/) — Martin Smith, May 25 '11 at 12:31

score 1 · Answer 2 · answered May 25 '11 at 11:08

@Digiguru

OMG, this really is the wholy grail!

WITH cteCustomers
AS ( SELECT id,
         Row_Number() OVER(ORDER BY Age DESC) AS Row,
         Row_Number() OVER(ORDER BY id ASC)
            + Row_Number() OVER(ORDER BY id DESC) - 1 AS Total /*<- voodoo here*/
      FROM   @Customer
      WHERE  employed = 1
      /*Imagine I've joined to loads more tables with a really complex where clause*/
   )
SELECT  name,  age,  Total
  /*This is where I choose the columns I want to read, it returns really fast!*/
FROM cteCustomers
INNER JOIN @Customer cust
ON cust.id = cteCustomers.id
WHERE row BETWEEN ( @PageSize * @PageNumber - 1 ) AND ( @PageSize * ( @PageNumber ) )
ORDER BY row ASC

So obvious now.

I wouldn't get too excited about this one. [See some performance figures here](http://www.sqlservercentral.com/articles/paging/70120/) — Martin Smith, May 25 '11 at 12:24

How do I get rowcount of a cte in a separate dataset?

2 Answers2

Linked