Page records using SQL

Shows usage of CommonTableExpression and Row_Number()

Date Revised: August 14 2009

SQL Server provides an easy way to page thru result sets. This means no more passing all of the records from the database server to your web server for processing each time paging is used.

-- Page data with SQL server

-- Use WITH to create a common table expression

-- and Row_Number() to number each record.

DECLARE @RecStart    INT;

DECLARE @RecEnd      INT;

SET @RecStart = 1;

SET @RecEnd = 20;

-- Specifies a temporary named result set, known as a

-- common table expression (CTE). This is derived from a

-- simple query and defined within the execution scope of a

-- single SELECT, INSERT, UPDATE, MERGE, or DELETE statement.

WITH CommonTableName AS

    SELECT fieldName,

    (ROW_NUMBER() OVER (ORDER BY fieldName)) AS RowNumber

    FROM dbo.TableName

     WHERE fieldName LIKE 's%' -- optional where clause

    GROUP BY fieldName -- optional grouping

-- SELECT the family names from the C.T.E above and

-- use the WHERE clause to limit rows returned.

SELECT fieldName

FROM CommonTableName

WHERE RowNumber BETWEEN @RecStart AND @RecEnd;