SQL 2005 Server Side Paging using CTE (Common Table Expression)(softscenario.blogspot.com)
submitted by 
animaonline(275) 4 years, 3 months ago
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
|category: C#
|Views: 27
tags:
Scenario table Common CTE C# another
Everyones tags:
Your Tags: