Thursday, 4 July 2019

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

                       






No comments:

Post a Comment

If you have any doubt, please let me know.

Popular Posts