SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

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

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 (
  rownumber = row_number() over (order by name asc), *
 from Production.Product
  ProductNumber like @productnumber + '%'
  and ProductSubcategoryID = @ProductSubcategoryID
 top (@rows_per_page) *,
 (select max(rownumber) from sqlpaging) as totalrows
from sqlpaging
where rownumber between @start and @end


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

SQL paging using ROW_NUMBER() SQL Server function

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.