Paging with Ranking Functions

Paging through result sets is a very common need in applications. It provides a convenient way to deliver small chunks of data to the client application, minimizing network traffic and allowing end users to browse data in page size format.

There are different methods to accomplish the paging, both on client and server side. The introduction of the ranking functions in SQL Server 2005 (and SQL Server 2008) provides another efficient tool to implement paging.

The following example demonstrates paging utilizing the ROW_NUMBER function. Here it helps to generate sequence for each row ordered by the loan date column, and the sequence is later used to split the result set into pages.

CREATE TABLE Loans (
  loan_nbr INT NOT NULL PRIMARY KEY,
  loan_date DATETIME NOT NULL,
  loan_amount DECIMAL(12, 2) NOT NULL
              DEFAULT 0.0,
  customer_nbr INT NOT NULL,
  loan_type CHAR(1) NOT NULL
            DEFAULT 'P'
            CHECK (loan_type IN ('P',  -- personal
                                 'B')) -- business
  );
 
INSERT INTO Loans 
VALUES (1, '20080801', 12000.00, 2, 'P'),
       (2, '20080805', 15700.00, 1, 'B'),
       (3, '20080610', 12000.00, 3, 'P'),
       (4, '20080401', 5000.00, 1, 'P'),
       (5, '20080715', 25000.00, 4, 'B'),
       (6, '20080610', 25000.00, 5, 'P'),
       (7, '20080501', 1000.00, 6, 'P'),
       (8, '20080810', 6000.00, 7, 'B'),
       (9, '20080815', 2000.00, 8, 'B'),
       (10, '20080815', 1000.00, 9, 'P'),
       (11, '20080715', 5500.00, 10, 'P'),
       (12, '20080615', 1000.00, 11, 'B'),
       (13, '20080820', 6000.00, 12, 'P'),
       (14, '20080510', 28000.00, 6, 'B'),
       (15, '20080815', 2000.00, 10, 'P'),
       (16, '20080810', 1500.00, 8, 'P'),
       (17, '20080817', 10000.00, 10, 'B'),
       (18, '20080816', 2500.00, 9, 'P');   
 
-- Paging
DECLARE @page_size INT = 5;
DECLARE @page_nbr  INT = 4;
 
WITH LoansRanked (loan_date, loan_amount, loan_type, seq) 
AS
(SELECT loan_date, loan_amount, loan_type,
        ROW_NUMBER() OVER (ORDER BY loan_date, loan_nbr)
FROM Loans)
SELECT loan_date, loan_amount, loan_type, seq 
FROM LoansRanked
WHERE seq > (@page_nbr - 1) * @page_size 
  AND seq <= @page_nbr * @page_size;
 
/*
 
Results (4th page which contains only 3 rows):
 
loan_date               loan_amount   loan_type seq
----------------------- ------------- --------- ----
2008-08-16 00:00:00.000      2500.00  P         16
2008-08-17 00:00:00.000     10000.00  B         17
2008-08-20 00:00:00.000      6000.00  P         18
 
*/

Labels: , ,