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

common-table-expression, performance, sql-server-2008

Solution

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

Problem

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.

Original source

Related problems