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.