The GuruByte Blog

A typical programming task is to provide a list of items on a webpage in a gridview, datagrid or other grid-based display element. The .Net framework dataset paging typically loads all records into server memory then displays only the records determined by the gridview/datagrid/pager’s page and pagesize settings. This can be inefficient when there are large datasets because most of the records are discarded after they are loaded. While there are many solutions to this problem, I discovered an elegant solution using SQL Server 2005+ CTE features.

The following solution uses a table expression combined with the Row_Number() function to select a page of data from a table based on user-supplied pagesize and pagenumber.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
– =============================================
– Author:  Brian Foote
– Create date: September 12, 2009
– Description: Returns @Pagesize Users for @Pagenumber (zero-based)
– =============================================
CREATE PROCEDURE [dbo].[procUserFindAll] (@Pagesize int
, @Pagenumber int)
AS
BEGIN
 Declare @FirstRow int
 Declare @LastRow int

 Select @FirstRow = @Pagenumber * @Pagesize + 1,
  @LastRow = @PageNumber * @PageSize + @PageSize ;

 with PagedData as
 (
  SELECT [UserID]
    ,[uContactID]
    ,[Username]
    ,[Password]
    ,[uSecurityGroupID]
    ,[Inactive]
    ,[DCreated]
    , Row_Number() Over (Order by UserID asc) As RowNumber
    FROM [dbo].tblUsers
 )
 SELECT [UserID]
    ,[uContactID]
    ,[Username]
    ,[Password]
    ,[uSecurityGroupID]
    ,[Inactive]
    ,[DCreated]
   FROM PagedData
   WHERE RowNumber Between @FirstRow and @LastRow
   Order By RowNumber asc;
END
GO