SQL paging using ROW_NUMBER() SQL Server Function
SQL paging in web applications like ASP.NET web sites, etc is a common requirement for SQL Server developers. For SQL Server versions before SQL Server 2012, for example paging in SQL Server 2005 can be implemented using ROW_NUMBER() SQL Server function easily.
With SQL Server 2012, aka SQL Denali a new enhancement in Transact-SQL ORDER BY clause helped developers to create sql paging query. Please refer to linked T-SQL tutorial for paging in SQL Server 2012
Here sql developers can find t-sql codes of a sample stored procedure which is created on SQL Server 2008 R2 sample database AdventureWorks2008R2. The below sql stored procedure filters rows stored in database table Production.Product and returns a limited number of rows.
The sample stored procedure accepts two important parameters to build paging in SQL Server 2008. Actually these parameters @rows_per_page for number of rows in page and @page_number for number of page are required for every sql paging solution.
Other sql parameters are for querying data stored in database table Product (for filtering or to search among table data)
CREATE PROCEDURE sp_SearchProduct (
@ProductSubcategoryID int,
@ProductNumber nvarchar(25),
@rows_per_page smallint,
@page_number smallint
)
--declare @rows_per_page smallint = 10
--declare @page_number smallint = 2
as
declare @start int, @end int
set @start = (@page_number - 1) * @rows_per_page + 1
set @end = @page_number * @rows_per_page
declare @src_productnumber nvarchar(30)
set @src_productnumber = isnull(@productnumber,'') + '%'
;with sqlpaging as (
select
rownumber = row_number() over (order by name asc), *
from Production.Product
where
ProductNumber like @productnumber + '%'
and ProductSubcategoryID = @ProductSubcategoryID
)
select
top (@rows_per_page) *,
(select max(rownumber) from sqlpaging) as totalrows
from sqlpaging
where rownumber between @start and @end
GO
Please note that within the SQL CTE (Common Table Expression) part of the SQL query, the filtering by WHERE clause is done. Besides what is most important here for sql paging is the use of SQL Server ROW_NUMBER() function (which is first introduced with SQL Server 2005).
The ROW_NUMBER() function returns a sort number named as rownumber column with the CTE structure. Outside the CTE, the row number column is filtered and rows with values belonging to specific page is returned back as result set.
Below T-SQL codes execute the above SQL paging stored procedure with different page numbers. The first execute statement returns table records that will be displayed in the first page. The second execute stored procedure statement returns rows for the second page, etc.
EXEC sp_SearchProduct 17, '%', 5, 1 -- First page
EXEC sp_SearchProduct 17, '%', 5, 2 -- Second page
EXEC sp_SearchProduct 17, '%', 5, 3 -- Page number 3
