Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

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 (
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.

Comments

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.