Custom Pagination using Row_Number()

SQL 2005 introduced a lot of new functionality that greatly changed the way we write code.  One of these code enhancements was the new function called Row_Number(), http://msdn.microsoft.com/en-us/library/ms186734.aspx.  The Row_Number() function gives developers a mechanism to sequentially order data based on several factors.  This post will demonstrate how to incorporate custom pagination using the Row_Number() function.  For a more in-depth guide please look at the detailed article I wrote on SQL Server Central, http://www.sqlservercentral.com/articles/SQL+Server+2005/65256/.

Let’s have a look at how the Row_Number() function works.

DECLARE @Row_Number_Test TABLE(
FNAME VARCHAR(25),
LNAME VARCHAR(25)
)

INSERT INTO @Row_Number_Test ([FNAME],[LNAME]) VALUES ('Adam','Haines');
INSERT INTO @Row_Number_Test ([FNAME],[LNAME]) VALUES ('John','Doe');
INSERT INTO @Row_Number_Test ([FNAME],[LNAME]) VALUES ('Mr.','Smith');

SELECT 
    ROW_NUMBER() OVER(ORDER BY LName,FName) AS RowNbr,
    FNAME,
    LNAME
FROM @Row_Number_Test

Results:

As you can see, the results render a sequential row number for each row, based on the value we specified in our order by clause.  Now that we have our hands on the row number, we can effectively filter the data for a specific set of pagination rows.

Let’s filter the data by using a specific page and page size. I chose to use the first page, where each page contains 10 rows.

DECLARE @pgSize INT,
        @pgNbr INT
        
SET @pgNbr = 1
SET @pgSize = 10

;WITH cte
AS
(
SELECT 
    ROW_NUMBER() OVER(ORDER BY LName,FName) AS RowNbr,
    FNAME,
    LNAME
FROM @Row_Number_Test
)
SELECT 
    RowNbr,
    FNAME,
    LNAME
FROM [cte]
WHERE 
    RowNbr >= (@pgSize * @pgNbr) - (@pgSize -1) AND 
    RowNbr <= @pgSize * @pgNbr

Results:

The results show that the eleventh row was excluded from the result set. You can play with the page size and page number to see how it affects the results. 

There you have it, custom pagination using the Row_Number() function.  More a more detailed evaluation of this method, take a look at the article on SQL Server Central, http://www.sqlservercentral.com/articles/SQL+Server+2005/65256/