Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


SQL Server Pagination

SQL Pagination

This article describes coding Pagination in SQL Server. The new method introduced with SQL Server 2012 uses the keywords offset and fetch, however, there is a couple of methods to Page through data prior to this addition to T-SQL for SQL versions prior to 2012. It is helpful to deliver chunks of data out of the database to ASP .Net, MVC, and other web applications as well as paging data in MS Access forms or delivering multiple worksheets to an Excel file from SQL Server.

SQL Server Pre-2012 Pagination

Paging data out of the SQL Server has been possible using a temp table with an Identity column (SELECT id=IDENTITY (int, 1,1)) or by using a table variable with an identity column as shown in the code below. Data inserted into the table variable could then be sorted in an ORDER BY clause and then extracted using the ID numbers for a portion of the data.

 Declare @prodTable TABLE (
  id int identity not null primary key clustered,
  ProductID int null,
  ProductName varchar(255) null)

 INSERT INTO @prodTable
 SELECT ProductID, Name
 from Production.Product
 order by Name;

 SELECT * FROM @prodTable
 WHERE ID Between 10 and 20

Old School SQL Server Paging with Table Variable

SQL Server 2012 Pagination

Paging data from SQL Server using the offset and Fetch keywords is easy to do. The sample below shows how to use a Row_number to retreive the first 10 and next 10 rows from the Production.Product table in AdventureWorks2014.

select row_number() over (order by Name asc) as RowNo,
 ProductID, Name
 from Production.Product
 order by rowNo offset 0 rows fetch next 10 rows only

select row_number() over (order by Name asc) as RowNo,
 ProductID, Name
 from Production.Product
 order by rowNo offset 10 rows fetch next 10 rows only

SQL Paging Sample

Another way to retrieve the pages of data might use a While loop to cycle through all of the data and deliver the pages based on variables being input from the application for the Offset (and Row Numbers, too, if desired) or delivering all pages out to an application (like worksheets in Excel). The sample below delivers all pages using a While loop.

declare @i as int, @ids as int
 set @i = 0
 select @ids=count(ProductID) from Production.Product

while (@i < @ids)
 begin
  select row_number() over (order by Name asc) as RowNo,
  ProductID, Name
  from Production.Product
  order by rowNo offset @i rows fetch next 100 rows only


  set @i = @i + 100

end

Pagination using Row_Number() & Offset \ Fetch

Another example of paging might use a Sort by Rank. This sample uses the Product Category name and list price for the sort and iterates through the Pages of Data as 100 Rows at a time.

declare @i as int, @ids as int
 set @i = 0
 select @ids=count(ProductID) from Production.Product

while (@i < @ids)
 begin
  select rank() over (partition by pc.Name order by ListPrice desc) as RowNo,
  ProductID, p.Name, ListPrice, pc.Name
  from Production.Product p inner join Production.ProductSubcategory ps
  on ps.productsubcategoryid = p.productsubcategoryid
  inner join Production.ProductCategory pc on pc.productcategoryid = ps.productcategoryid
  order by rowNo offset @i rows fetch next 100 rows only

  set @i = @i + 100

end

SQL Pagination by Rank