Hello Friends, In this article, We will learn Sql Fetch and Offset in Sql and Server Side Paging Logic
OFFSET and FETCH clauses are the options of the ORDER BY clause.
OFFSET is used to skip specified number of rows return by query and FETCH is used to get specified number of rows return by query. Like in skip and take extension methods of LINQ in C# respectively.
Syntax:
ORDER BY order_by_expression [ COLLATE collation_name ] [ ASC | DESC ] [ ,...n ] [
] ::= { OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS } [ FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY ] }
To understand OFFSET lets create a table and insert few rows init as shown below.
CREATE TABLE dbo.Sales
(
Id Int identity(1,1) Primary Key,
Executive varchar(300),
TotalSales decimal(18,2),
SalesForYear int
)
Go
Inserting some data into Sales table.
INSERT INTO dbo.Sales Values('Jack',250000,2016),('Jack',25340,2017),('Jack',556000,2018),('Jack',770650,2019)
INSERT INTO dbo.Sales Values('Wilson',450000,2016),('Wilson',35340,2017),('Wilson',556000,2018),('Wilson',564364,2019)
INSERT INTO dbo.Sales Values('Andrew',334450,2016),('Andrew',567832,2018),('Andrew',45673,2019)
INSERT INTO dbo.Sales Values('Samith',234354,2016),('Samith',2346876,2017),('Samith',346575,2018),('Samith',77533,2019)
INSERT INTO dbo.Sales Values('Hoper',456865,2016),('Hoper',455560,2017),('Hoper',890765,2018)
Go
SELECT * FROM dbo.Sales
Go
Output: Id Executive TotalSales SalesForYear ----------- ---------------------------- --------------------------------------- ------------ 1 Jack 250000.00 2016 2 Jack 25340.00 2017 3 Jack 556000.00 2018 4 Jack 770650.00 2019 5 Wilson 450000.00 2016 6 Wilson 35340.00 2017 7 Wilson 556000.00 2018 8 Wilson 564364.00 2019 9 Andrew 334450.00 2016 10 Andrew 567832.00 2018 11 Andrew 45673.00 2019 12 Samith 234354.00 2016 13 Samith 2346876.00 2017 14 Samith 346575.00 2018 15 Samith 77533.00 2019 16 Hoper 456865.00 2016 17 Hoper 455560.00 2017 18 Hoper 890765.00 2018 (18 rows affected)
OFFSET:
Let Skip first 10 rows we will apply following query to achieve the result.
SELECT *
FROM dbo.sales
ORDER BY Id
OFFSET 10 ROW
Go
Output: Id Executive TotalSales SalesForYear ----------- ----------------- --------------------------------------- ------------ 11 Andrew 45673.00 2019 12 Samith 234354.00 2016 13 Samith 2346876.00 2017 14 Samith 346575.00 2018 15 Samith 77533.00 2019 16 Hoper 456865.00 2016 17 Hoper 455560.00 2017 18 Hoper 890765.00 2018 (8 rows affected)
FETCH NEXT:
Lets skip first 5 rows and take next 5 rows only.
SELECT *
FROM dbo.sales
ORDER BY Id
OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY
Go
Output: Id Executive TotalSales SalesForYear ----------- ---------------------- --------------------------------------- ------------ 6 Wilson 35340.00 2017 7 Wilson 556000.00 2018 8 Wilson 564364.00 2019 9 Andrew 334450.00 2016 10 Andrew 567832.00 2018 (5 rows affected)
Lets create a Server Side Paging Logic.
Following query will skip first 5 record(s) and fetch next 5 record(s) only in every itration.
DECLARE @TotalRecords int=0
DECLARE @PageSize int = 5
DECLARE @PageIndex int = 1
SELECT @TotalRecords = COUNT(1) FROM dbo.sales
WHILE Round((CAST(@TotalRecords AS float)/CAST(@PageSize AS float)),0) >= @PageIndex
BEGIN
SELECT *
FROM dbo.sales
ORDER BY Id
OFFSET (@PageIndex-1)*@PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY
SET @PageIndex +=1
END
Go
Output: Id Executive TotalSales SalesForYear ----------- -------------------- --------------------------------------- ------------ 1 Jack 250000.00 2016 2 Jack 25340.00 2017 3 Jack 556000.00 2018 4 Jack 770650.00 2019 5 Wilson 450000.00 2016 (5 rows affected) Id Executive TotalSales SalesForYear ----------- -------------------- --------------------------------------- ------------ 6 Wilson 35340.00 2017 7 Wilson 556000.00 2018 8 Wilson 564364.00 2019 9 Andrew 334450.00 2016 10 Andrew 567832.00 2018 (5 rows affected) Id Executive TotalSales SalesForYear ----------- -------------------- --------------------------------------- ------------ 11 Andrew 45673.00 2019 12 Samith 234354.00 2016 13 Samith 2346876.00 2017 14 Samith 346575.00 2018 15 Samith 77533.00 2019 (5 rows affected) Id Executive TotalSales SalesForYear ----------- -------------------- --------------------------------------- ------------ 16 Hoper 456865.00 2016 17 Hoper 455560.00 2017 18 Hoper 890765.00 2018 (3 rows affected)
If you have any query or question or topic on which, we might have to write an article for your interest or any kind of suggestion regarding this post, Just feel free to write us, by hit add comment button below or contact via Contact Us form.
Your feedback and suggestions will be highly appreciated. Also try to leave comments from your valid verified email account, so that we can respond you quickly.