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
- 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)
- 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.
No comments:
Post a Comment
If you have any doubt, please let me know.