SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Getting a Page of Results

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 (
WHEN @SortOrder = 'Title' THEN ROW_NUMBER()OVER (ORDER BY Title)
WHEN @SortOrder = 'HireDate' THEN ROW_NUMBER()OVER (ORDER BY HireDate)
-- In all other cases, assume that @SortOrder = '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.

The Voice of the DBA

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


Posted by Anonymous on 5 May 2010

Pingback from  Twitter Trackbacks for                 SQL Server Central, Getting a Page of Results - SQL Musings         [sqlservercentral.com]        on Topsy.com

Posted by Paul White NZ on 7 May 2010

The CASE @SortOrder idea looks clever, but it does not scale well.  Check the query plan: SQL Server 2005 has to sort the whole set multiple times - once for every ROW_NUMBER expression in the query.

This is the same reason that the 'double row number' row-counting approach sucks (see Part II of my series).

The CASE idea is workable in SQL Server 2008 SP1 CU5 onward.  I'll be covering that in part III.

Leave a Comment

Please register or log in to leave a comment.