Sunday, July 17, 2016

SQL - OFFSET-FETCH Clause

The OFFSET-FETCH clause is a really very cool feature which is introduced in SQL Server 2012 to produce multi page results or pagination with SQL. This feature comes in the limelight, if you want to display only 20-30 records on the single page and want to add pagination for further more records. This was happening in the code page but it could be possible in SQL Server by using OFFSET-FETCH clause and improve the performance of the program.

Limits of OFFSET-FETCH clause
This feature works fabulous but it has some limitations which are given below-
  1. It works in SQL Server 2012+ onwards only.
  2. It excludes the first set of records and ORDER BY is mandatory to use it.
  3. TOP cannot be combined with OFFSET and FETCH in the same query expression.
  4. The OFFSET/FETCH rowcount expression can be any arithmetic, constant, or parameter expression that will return an integer value. The rowcount expression does not support scalar sub-queries.
  5. OFFSET with FETCH NEXT returns a defined number of records.
  6. OFFSET with FETCH NEXT is great for building pagination support.
Syntax 1
[ORDER BY { order_by_expression [ ASC | DESC ] } [ ,...n][<offset_fetch>] ]

Syntax 2
<offset_fetch>::= {OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }    [FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONL Y]}
Arguments
OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
Specifies the number of rows to skip, before starting to return rows from the query expression.
FETCH { FIRST|NEXT } <rowcount expression> { ROW|ROWS } ONLY
Specifies the number of rows to return, after processing the OFFSET clause. The argument for the FETCH clause can be an integer or expression that is greater than or equal to one. You can use ROW and ROWS interchangeably. Similarly, FIRST and NEXT can be used interchangeably.
To understand this feature, we can take an example of 20 records by using common table expression and you can use the actual data table.
1. Pull Records with OFFSET without using FETECH NEXT
USE tempdb
GO
---- Common Table Expression
;WITH CTE AS
(
SELECT 1 AS ID, 'Abc '+CONVERT(VARCHAR(20),1)  Name
UNION ALL
----- Adding 20 records
SELECT ID+1 AS ID,  Name='Abc '+CONVERT(VARCHAR(20),ID+1)
FROM CTE WHERE ID<20
)
----- Pull Records with OFFSET without using FETECH NEXT
SELECT * FROM CTE
----- Use Order By clause
ORDER BY ID DESC
----- Pull Records with OFFSET
OFFSET 10 ROWS
ID
Name
10
Abc 10
9
Abc 9
8
Abc 8
7
Abc 7
6
Abc 6
5
Abc 5
4
Abc 4
3
Abc 3
2
Abc 2
1
Abc 1

2. Pull Records with OFFSET with using FETECH NEXT
USE tempdb
GO
---- Common Table Expression
;WITH CTE AS
(
SELECT 1 AS ID, 'Abc '+CONVERT(VARCHAR(20),1)  Name
UNION ALL
----- Adding 20 records
SELECT ID+1 AS ID,  Name='Abc '+CONVERT(VARCHAR(20),ID+1)
FROM CTE WHERE ID<20
)
----- Pull Records with OFFSET with FETECH NEXT
SELECT * FROM CTE
----- Use Order By clause
ORDER BY ID DESC
----- Pull Records with OFFSET
OFFSET 5 ROWS
---- Set FETECH NEXT with 5 Rows
FETCH NEXT 5 ROWS ONLY
ID
Name
15
Abc 15
14
Abc 14
13
Abc 13
12
Abc 12
11
Abc 11

3. Pull Records with OFFSET - FETECH NEXT with variables

USE tempdb
GO
----- local Variables
declare @PageCounter Int=0, @TotalRows int=20, @RecordsToDisplay int=5

---- Set page counter
Set @PageCounter=@TotalRows/@RecordsToDisplay

---- Common Table Expression
;WITH CTE AS
(
SELECT 1 AS ID, 'Abc '+CONVERT(VARCHAR(20),1)  Name
UNION ALL
----- Adding 20 records
SELECT ID+1 AS ID,  Name='Abc '+CONVERT(VARCHAR(20),ID+1)
FROM CTE
---- Set Rows count for recursive
WHERE ID<@TotalRows
)
----- Pull Records with OFFSET with FETECH NEXT
SELECT * FROM CTE
----- Use Order By clause
ORDER BY ID DESC
----- Pull Records with OFFSET
OFFSET @PageCounter ROWS
---- Set FETECH NEXT with 5 Rows
FETCH NEXT @RecordsToDisplay ROWS ONLY
ID
Name
16
Abc 16
15
Abc 15
14
Abc 14
13
Abc 13
12
Abc 12
Conclusion
By using this feature, we can be easily avoided on the fly calculations in the programs because these things can be handle in the SQL Server. This feature is not applicable lower versions of SQL Server and usable in SQL Server 2012+ on wards.

No comments:

Post a Comment

Popular Posts