Database :: Paging Result Sets

Posted 2007-02

Writing a simple web site usage application recently, I had to implement something very common to any web application - returning a subset of records that allows users to page through a large recordset. MySQL and SQLite SELECT statements both include a handy LIMIT clause that makes the task both simple and easy to read. (sample data) In SQLite:

-- create table and load data from sqlite command prompt:
CREATE TABLE hitcount (hits int,bytes int);
.mode tabs
.import "hitcount.txt" hitcount

-- simple query, webapp uses placeholders (LIMIT ? OFFSET ?)
SELECT * FROM hitcount ORDER BY hits DESC,bytes DESC
  LIMIT 2 OFFSET 0;
SELECT * FROM hitcount ORDER BY hits DESC,bytes DESC
  LIMIT 2 OFFSET 2;

Which gives the desired results:

67867   8796745
67678   67855656

47843   89078976
23423   78786789

In SQL Server, things aren't as easy or intuitive. SQL purists will say the above solution is flawed because there is no concept of ordered rows in a real DBMS. To which I would reply "who cares?!?" At the end of the day all I care about is getting the job done in a simple, well readable and maintainable fashion.

In SQL Server 2000, there are many different ways to get the job done. (see links below) One way is to use SELECT @local_variable and the SET ROWCOUNT option:

-- after the same table creation process and data as SQLite example above
-- load data
BULK INSERT hitcount FROM 'DIRECTORY\hitcount.txt'

CREATE PROCEDURE twoK_pager_sp
  @offset   int=1,
  @per_page int=10 -- default 10 per page
AS

-- local variable to limit result set
DECLARE @hits int

-- stop processing after @offset number of rows
SET ROWCOUNT @offset
-- returns list of values, so @hits assigned LAST value returned
  SELECT @hits=hits FROM hitcount
  ORDER BY hits DESC,bytes DESC

-- results limited to desired records per page
SET ROWCOUNT @per_page
  SELECT hits,bytes FROM hitcount
-- at the correct offset
  WHERE hits<=@hits
  ORDER BY hits DESC,bytes DESC

-- reset everything back to the default!
SET ROWCOUNT 0

-- same results as SQLite example
exec twoK_pager_sp  @offset=1, @per_page=2
exec twoK_pager_sp  @offset=3, @per_page=2

In SQL Server 2005, (tested on 2005 Express, anyway - I don't have access to the full version) the ROW_NUMBER function imposes row order on a result set. Think of it like adding a separate IDENTITY column. Try the following self-explanatory sample and you'll see what I mean:

CREATE PROCEDURE two05_pager_sp
  @lbound   int=NULL,
  @ubound   int=NULL
AS

SELECT * FROM (
  SELECT hits,bytes, ROW_NUMBER()
  OVER (order by hits desc,bytes desc) as row_number
  FROM hitcount
  ) AS whatever_alias_you_choose 
WHERE row_number between @lbound and @ubound;

-- same results as SQLite example
EXEC two05_pager_sp @lbound=1,@ubound=2
EXEC two05_pager_sp @lbound=3,@ubound=4

Links

Code

Other