Paging records using SQL Server 2005

In this article we would look into basics of paging records in SQL Server 2005. I have provided couple of methods with correspondng code snippets for our better understanding.

Code snippet for the sample table:

Create Table tstSQLPaging
(
Sno int,
FirstName varchar(50),
LastName varchar(50),
EmailId varchar(100),
Salary int
)
Go

Enter sample data into that table:

Insert into tstSQLPaging values (1, 'Vadivel','M','vmvadivel@yahoo.com',10000)
Insert into tstSQLPaging values (2, 'Sailakshmi','L','abc@yahoo.com',9000)
Insert into tstSQLPaging values (3, 'Raj','A','aRaj@yahoo.com',11000)
Insert into tstSQLPaging values (4, 'Dhina','B','bDhina@yahoo.com',25000)
Insert into tstSQLPaging values (5, 'Siddharth','s','itissiddhu@yahoo.com',6000)
Insert into tstSQLPaging values (6, 'Vicky','L','vicky@yahoo.com',19000)
Insert into tstSQLPaging values (7, 'Tejas','P','tp@yahoo.com',22000)
Insert into tstSQLPaging values (8, 'Revathy','L','rev@yahoo.com',4000)
Go

Method 1:

Extract from MSDN ::
Row_number() - Returns a running count of all rows in the specified scope.

With the help Row_Number function we get the running count of all the rows and using the between clause we filter the records as per our need.

With SQLPaging As
(
Select Row_number() Over (Order by Sno) as RunningNumber, FirstName, LastName, EmailId
from tstSQLPaging
)
Select * from SQLPaging
where RunningNumber between 2 and 3

Method 2:

This method is much more advanced (!!), we get the page size and page number as input and then with the help of Row_Number function filter out data pertaining that particular page.

Create Proc uspFetchPagedData
(
@pPageSize as int,
@pPageNum int
)
As

With SQLPaging2
As
(
Select Top(@pPageSize * @pPageNum) ROW_NUMBER() Over (Order by Sno) as RunningNumber,
FirstName, LastName, EmailId
From tstSQLPaging
)
Select * from SQLPaging2 where RunningNumber > ((@pPageNum - 1) * @pPageSize)

Testing:

i) Exec uspFetchPagedData 6,2
ii) Exec uspFetchPagedData 5,2