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
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
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
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
