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

Remote desktop stops working

Suddenly my workstation wouldn't accept incoming remote desktop sessions. I first thought it was because I was connecting from a Vista computer, but as it turned out, I couldn't even connect from other 2003 servers. The event log only said this:

Application popup: : \SystemRoot\System32\RDPDD.dll failed to load

Which was not very helpful! After googling around quite a bit, I had tried reinstalling the RDP software's latest version and rebooted, when I found multiple people describing the same problem and fixing it by removing their ATI video drivers... As I have a nvidia card, I first thought it had nothing to do with this. However... there wasn't much else to try. I uninstalled the Asus Enhanced Video Driver and the nvidia drivers - and then it worked. Crazy. Worst part is, now I've reinstalled the video driver, and remote desktop still works. Huh.

So it could be that Asus Enhanced Video Driver is the culprit. I won't try and find out.

IIS and .net 3.5 beta 2

We installed .net 3.5 beta 2 on a web server this morning. It did not work. Even our 2.0 webs stopped working. So to save others the trouble...
Go ahead to
%WINDIR%\Microsoft.NET\Framework\v.2.0.something...\CONFIG\web.config

Go ahead and remove all instances of xmlns="" and you're good to go!

Also worth mentioning, 3.5 and 3.0 does not appear in the ASP.NET tag on IIS, only 2.0 does. This is because 3.0 and 3.5 are extensions of the 2.0 framework.