SQL 2005 Server Side Paging using CTE (Common Table Expression)

When a application want to show the result from an SQL-query as a paged view, i.e. showing ten or fifteen result on first page, then have a "next" function to show the next page of results.

To minimize traffic over the network, it is best practice to do the paging on the SQL-server, so that only the results you want to show is sendt to the application.

With SQL Server 2005 this is quite easy using the new CTE capabilities and the new ROW_NUMBER() function.

(using the AdventureWorks example database for SQL-2005 http://codeplex.com/)

Consider the following T-SQL to select out all employees from Person.Contact:

SELECT [FirstName]
,[MiddleName]
,[LastName]
,[EmailAddress]
FROM [Person].[Contact]

This will result in 19972 rows returned, and the paging logic has to be done on the client application. not good.. so first we implement the ROW_NUMBER() function like this:

SELECT ROW_NUMBER() OVER (Order by [Person].[Contact].[LastName]) AS RowID,
[FirstName]
,[MiddleName]
,[LastName]
,[EmailAddress]
FROM [Person].[Contact]

This will create a unique RowID for each row in the result.

Now we need to wrap the result in a CTE using just the WITH [ctename] AS () statement:

WITH AllEmployees AS
(SELECT ROW_NUMBER() OVER (Order by [Person].[Contact].[LastName]) AS RowID,
[FirstName]
,[MiddleName]
,[LastName]
,[EmailAddress]
FROM [Person].[Contact])

Now we have all the Employees in a in-memory table called AllEmployees, and we can select from this table as any other table, with all the common clauses. Simplest term:

SELECT [FirstName]
,[MiddleName]
,[LastName]
,[EmailAddress]
FROM AllEmployees

Then, to use this for a Server-side paging solution, the simplest way we use the RowID to establish what rows to return, either by using DECLARE [varname] or by putting the whole code into a Parameterized StoredProcedure like this:

CREATE PROC GetPagedEmployees (@NumbersOnPage INT=25,@PageNumb INT = 1)
AS BEGIN

WITH AllEmployees AS
(SELECT ROW_NUMBER() OVER (Order by [Person].[Contact].[LastName]) AS RowID,
[FirstName],[MiddleName],[LastName],[EmailAddress] FROM [Person].[Contact])

SELECT [FirstName],[MiddleName],[LastName],[EmailAddress]
FROM AllEmployees WHERE RowID BETWEEN
((@PageNumb - 1) * @NumbersOnPage) + 1 AND @PageNumb * NumbersOnPage
ORDER BY RowID

END

The parameter for this procedure is used for the paging and when executed it will return a result based on how many results per page, and what page number to return, like this:

EXEC GetPagedEmployees 15,2 -- will return 15 employees from id = 16 and up (page 2)

This technique can be used for all type of results where you want to return a fixed number of results, paged server-side.

/Leo