The paging and ranking functions introduced in 2005 are old news by now, but the typical ROW_NUMBER OVER() implementation only solves part of the problem.
Nearly every application that uses paging gives some indication of how many pages (or total records) are in the total result set. The challenge is to query the total number of rows, and return only the desired records with a minimum of overhead? The holy grail solution would allow you to return one page of the results and the total number of rows with no additional I/O overhead.
In theory, ROW_NUMBER() gives you all the information you need because it assigns a sequential number to every single row in your result set. It all falls down, of course, when you only return a subset of your results that don't include the highest sequential number. The solution is to return a 2nd column of sequential numbers, in the reverse order. The total number of the records will always be the sum of the two
fields on any given row minus 1 (unless one of your sequences is zero-bound).
DECLARE @startRow INT ; SET @startrow = 50
;
WITH cols AS
(
SELECT col1, col2,
ROW_NUMBER() OVER(ORDER BY col1, col2) AS seq,
ROW_NUMBER() OVER(ORDER BY col1 DESC, col2 desc) AS totrows
FROM Table1
)
SELECT col1, col2, totrows + seq -1 as TotRows
FROM cols
WHERE seq BETWEEN @startRow AND @startRow + 49
ORDERBY seq
This approach gives us our page of data and the total number of rows with zero additional overhead! (well, maybe one or two ms of CPU time, but that's it). 
No comments:
Post a Comment