Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

The other day I was looking over a couple of articles on paging, looking to see if I could learn something new in T-SQL. I’ve implemented some SQL2000 era paging systems, none of which performed wonderfully, so I checked out Jacob Sebastian's basic Server Side Paging and Paul White’s Optimizing Paging Part 1.

I’ve done systems similar to Jacob’s, but he had an interesting use of the OVER clause in his code. He had this code:

;WITH emp AS ( SELECT CASE WHEN @SortOrder = 'Title' THEN ROW_NUMBER()OVER (ORDER BY Title) WHEN @SortOrder = 'HireDate' THEN ROW_NUMBER()OVER (ORDER BY HireDate) WHEN @SortOrder = 'City' THEN ROW_NUMBER()OVER (ORDER BY City) -- In all other cases, assume that @SortOrder = 'LastName' ELSE ROW_NUMBER()OVER (ORDER BY LastName) END AS RecID, , LastName , FirstName , Title , HireDate , City , Country , PostalCode FROM employees

This is a great solution in SQL Server 2005. It’s much different than what I had done in SQL 2000, where I’d typically approach the problem by using the sort key to get the next page.

So say I had this data in a table (Customers):

CustomerID Customer

----------- ------------

1 Jones

2 Smith

3 Johnson

4 Allen

5 Gates

Then suppose I wanted page 1, 2 results per page, ordered by Customer. I would want to see “Allen, Gates” on page 1. I’d use this code.

select top 2 Customer

from Customers

Order By Customer

If I wanted page 2, I’d go here:

select top 2 Customer

from Customers

where Customer > ‘Gates’

Order By Customer

And this would get me “Johnson” and “Jones” since the WHERE clause would reset results. If you have control of the application code, and you can pass in the previous values, you can easily build pages like this.

If you switch orders, say to the CustomerID, then you can easily pass that in as well, and use that for ordering.

There is a downside, however. Any ideas? I’ll post that in my next look at paging.