Implementing Paging in SQL Server 2005 Stored Procedures

Normally for implementing Paging in Grid View, we enable the Paging property in GridView and will write code in the PageIndex Changed event. But the problem with this approach is it will fetch all the data from Database for Paging, will result wastage of network bandwidth and resources, also affect in the Page Performance. In SQL Server 2005 Microsoft introduced a new concepts called Row_Number() and Derived Table with the help these two we can move the Paging logic to Database instead of ASP.Net web forms. This concept also works with custom paging implementations for DataList or Repeater controls.

CREATE PROCEDURE usp_GetContacts
(@Page INT, @RecsPerPage INT)
AS
SELECT tblContacts.[Name], tblContacts.[IsParent] FROM
(SELECT ROW_NUMBER() OVER(ORDER BY [ID]) AS RowNumber,
[Name], [IsParent] FROM [TreeView].[dbo].[Contacts]) tblContacts
WHERE RowNumber > @RecsPerPage*(@Page) AND RowNumber <= @RecsPerPage*(@Page+1)

In this code we are creating a Column called “RowNumber”. And we are comparing the input parameters with the derived table tblContacts.

Thanks to Aneesh / Prasanth for their valueable suggestions and comments.