Friday February 5th

1 Kicks

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

0 comments

Commenting on Stories is limited for now and will open up to those recommended by the community. Learn how
Loading DotNetKicks...
brought to you by the Kicks Network