Friday, May 7, 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
AS
(
    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
    FROM [INFORMATION_SCHEMA].columns
)
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: