CTE, ROW_NUMBER and ROWCOUNT

Question

I am trying to return a page of data and also row count of all data in one stored procedure which looks like following:

WITH Props AS
(
    SELECT *,
    ROW_NUMBER() OVER (ORDER BY PropertyID) AS RowNumber
    FROM Property
    WHERE PropertyType = @PropertyType AND ...
)   

SELECT * FROM Props 
WHERE RowNumber BETWEEN ((@PageNumber - 1) * @PageSize) + 1 AND (@PageNumber * @PageSize);

I am unable to return the row count (highest row number).

I know this has already been discussed (I've seen this: Efficient way of getting @@rowcount from a query using row_number) but when I add COUNT(x) OVER(PARTITION BY 1) in the CTE, the performance degrades and the query above that normally takes no time takes forever to execute. I reckon it's because the count is calculated for each row? I seems that I can't reuse the CTE in another query. Table Props has 100k records, CTE returns 5k records.

Should retag as a SQL Server question. I would have done it for you, but you're only allowed 5 tags, and I didn't know which you would want to remove. — James Johnson, Sep 15 '11 at 19:59

xanatos · Accepted Answer · 2011-09-15T20:52:33.253

21

In T-SQL it should be

;WITH Props AS
(
    SELECT *,
        ROW_NUMBER() OVER (ORDER BY PropertyID) AS RowNumber
    FROM Property
    WHERE PropertyType = @PropertyType AND ...
)

, Props2 AS
(
    SELECT COUNT(*) CNT FROM Props
)

-- Now you can use even Props2.CNT
SELECT * FROM Props, Props2
WHERE RowNumber BETWEEN ((@PageNumber - 1) * @PageSize) + 1 AND (@PageNumber * @PageSize);

now you have CNT in every line... Or you wanted something different? You wanted a second resultset with only the count? Then do it!

-- This could be the second result-set of your query.
SELECT COUNT(*) CNT
FROM Property
WHERE PropertyType = @PropertyType AND ...

Note: reedited, the query 1 David was referencing now has been trashcanned, query 2 is now query 1.

edited Sep 15 '11 at 20:52

answered Sep 15 '11 at 20:07

xanatos

109,618
12
197
280

1. does not work because you'd have to have a group by clause in. 2. works perfect and I like it best. Thank you. – David Sep 15 '11 at 20:40
In my version I had COUNT(1) added in the first cte which caused 800000 reads on the worktable. Using a second cte (like you did here) skipped all those reads resulting in a blazingly fast query. – Guillaume Schuermans Aug 04 '15 at 15:51

score 1 · Answer 2 · edited Jan 14 '21 at 18:54

I had the same problem and wanted to share the code which would return page and total rows. The problem is fixed by a temporary table. Here is the body of the stored procedure:

DECLARE @personsPageTable TABLE(
  RowNumber INT, 
  PersonId INT, 
  FirstName NVARCHAR(50), 
  LastName NVARCHAR(50), 
  BirthDate DATE, 
  TotalCount INT);
    
        ;WITH PersonPage AS 
        (
            SELECT 
                 ROW_NUMBER() OVER(ORDER BY persons.Id) RowNumber,
                 Id,
                 FirstName,
                 LastName,
                 BirthDate
            FROM Persons
            WHERE BirthDate >= @BirthDateFrom AND BirthDate <= @BirthDateTo
        ), TotalCount AS( SELECT COUNT(*) AS [Count] FROM PersonPage)
        INSERT INTO @personsPageTable
        SELECT *, (select * from TotalCount) TotalCount FROM PersonPage
        ORDER BY PersonPage.RowNumber ASC
        OFFSET ((@pageNumber - 1) * @pageSize) ROWS
        FETCH NEXT @pageSize ROWS ONLY
    
        SELECT TOP 1 TotalCount FROM @personsPageTable
    
        SELECT 
            PersonId, 
            FirstName, 
            LastName, 
            BirthDate
        FROM @personsPageTable

As you can see I put CTE result and total rows into the temporary table and select two queries. The first return total count and the second return page with data.

SQLMenace · Answer 3 · 2011-09-15T20:12:54.303

1

You want the count for the whole resultset right?

does this work speedwise?

SELECT *,(select MAX(RowNumber) from Props) as MaxRow 
FROM Props 
WHERE RowNumber BETWEEN ((@PageNumber - 1) * @PageSize) + 1 
    AND (@PageNumber * @PageSize);

edited Sep 15 '11 at 20:12

answered Sep 15 '11 at 19:59

SQLMenace

132,095
25
206
225

CTE, ROW_NUMBER and ROWCOUNT

3 Answers3