Pagination in SQL Server using offset and fetch

Paging is nothing but a common technique which is used when we have large results sets. Suppose we have 100k records in the dataset and this data we need to render in the application. In the Interface page, we can’t show these records. In this case, we need to limit the data in the UI. It is generally a bad use of system resources to hold everything in memory when only a small subset of those records can be displayed at any given time.

With the help of pagination technique, we can get n number of records in one set. In SQL server 2012 Offset and Fetch clause introduce. This clause is part of the order by clause. With the help of offset and fetch clause, we will do the pagination.

See the example

For the demo I have used “AdventureWorksDW2012” Database. in this example I want to get the total sales amount on the product wise.

select pc.EnglishProductCategoryName As Product_Category, psc.EnglishProductSubcategoryName AS Product_Sub_Category_Name, p.EnglishProductName As Product_Name, CAST(SUM(FIS.SalesAmount) AS INT) AS Total_Sales_Amount, CAST(SUM(FIS.TaxAmt) AS INT) AS Total_TaxAmt, CAST(SUM(FIS.Freight) AS INT) AS Total_Freight From FactInternetSales FIS Inner join DimProduct P on P.ProductKey=FIS.ProductKey inner join DimProductSubcategory psc on p.ProductSubcategoryKey=psc.ProductCategoryKey Inner Join DimProductCategory pc on pc.ProductCategoryKey=psc.ProductCategoryKey Group by pc.EnglishProductCategoryName,psc.EnglishProductSubcategoryName, p.EnglishProductName order by pc.EnglishProductCategoryName,psc.EnglishProductSubcategoryName, p.EnglishProductName

                     
         
It returns 792 records.

Now as per the requirement we want to show only 10 records at a time in the UI.

There is two way

1. Either we get all data from DB and only show the 10 records in UI (in this case 782 records unnecessary sorted in the memory. It is used less)
2.    Or we need to send only 10 records from the DB at a time. When the user asks for next DB will return another next 10 records.

The 2^nd approach is good.

Let’s see how we can achieve this.

I am creating a Stored Procedure and this SP is called by UI.

CREATE PROCEDURE P_Get_Product_Total_sales  (  @PageNo INT,  @RowCountPerPage INT  ) AS SELECT pc.EnglishProductCategoryName As Product_Category, psc.EnglishProductSubcategoryName AS Product_Sub_Category_Name, p.EnglishProductName As Product_Name, CAST(SUM(FIS.SalesAmount) AS INT) AS Total_Sales_Amount, CAST(SUM(FIS.TaxAmt) AS INT) AS Total_TaxAmt, CAST(SUM(FIS.Freight) AS INT) AS Total_Freight From FactInternetSales FIS Inner join DimProduct P on P.ProductKey=FIS.ProductKey inner join DimProductSubcategory psc on p.ProductSubcategoryKey=psc.ProductCategoryKey Inner Join DimProductCategory pc on pc.ProductCategoryKey=psc.ProductCategoryKey Group by pc.EnglishProductCategoryName,psc.EnglishProductSubcategoryName,p.EnglishProductName order by pc.EnglishProductCategoryName,psc.EnglishProductSubcategoryName,p.EnglishProductName OFFSET (@PageNo - 1) * @RowCountPerPage ROWS FETCH NEXT @RowCountPerPage ROWS ONLY GO

SP created successfully.

This SP has two parameter

@PageNo : This we will get it from the UI page Number.

@RowCountPerPage : a number of rows we want to get the records.

Now I am executing this SP. I want to show 5 records in a page

EXEC P_Get_Product_Total_sales 1,5

   
                     
 

It displays 5 records. Next 5 records will be displayed in 2^nd page.

   

While we are using the offset and fetch clause we need to keep in mind.

Without order by clause, we can’t use this clause

Offset and Fetch clause is the part of the order by clause, without it we can’t use this. It will throw an error.

Let’s see

  

    

Order of order by clause

First of all, we need to sort our dataset then we can use offset and fetch clause. Otherwise, it will throw the error.

 

Number of rows provided for a FETCH clause must be greater than zero

If we will provide fetch value 0 or less than it then it will throw the error. This value must greater than 0.

 

Offset value can’t be negative

Offset clause value can’t be negative. It should be 0 or greater than it. If we gave negative value it will throw an error.

 

We can’t use the top clause with offset and fetch clause

We can’t use the top clause with offset and fetch. It will throw an error.

 

We can’t use offset without Fetch clause