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 Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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

SQL paging using ROW_NUMBER() SQL Server function







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems