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.
