Friday, 7 May 2010

TSQL : Paging results

Adapted from SSC's Paging : The Holy Grail
How to page a results set with minimum server load >

DECLARE @startRow INT ; 
DECLARE @resultsSize INT ; 

SET @startrow =  50
SET @resultsSize = 50

;WITH cols
    SELECT table_name, column_name,
        ROW_NUMBER() OVER(ORDER BY table_name, column_name) AS sequence,
        ROW_NUMBER() OVER(ORDER BY table_name DESC, column_name desc) AS totalRows
SELECT table_name, column_name, (totalRows + seq -1) as totalRows
FROM cols
WHERE sequence BETWEEN @startRow AND @startRow + (@resultsSize - 1)
ORDER BY sequence

No comments: