Using SQL Server's OFFSET and FETCH

Daily Knowledge Drop

SQL Server has two keywords available, OFFSET and FETCH which can be used to limit the number of rows returned by a query.

This functionality has been available in SQL Server 2012 and later, and Azure SQL Database.

Limiting rows returned

The OFFSET keyword can be used independently or in conjunction with the FETCH keyword, which cannot be used in isolation.

• OFFSET: determines how many rows to skip at the start of the dataset
• FETCH: determines how many rows to return, after the OFFSET rows have been skipped

Whether using only OFFSET or OFFSET + FETCH, in both situations, the ORDER BY clause is required.

Examples

In the following examples, the table has a simple auto incrementing int Id field which is used for ordering.

Constant value

-- select all ids order from smallest to largest
SELECT Id 
FROM OffSetDemo
ORDER BY Id ASC

-- Skip the first 10 rows, and return
-- all other rows
SELECT Id 
FROM OffSetDemo
ORDER BY Id ASC
OFFSET 10 ROWS

-- Skip the first 10 rows, and return
-- the next 20 rows only
SELECT Id 
FROM OffSetDemo
ORDER BY Id ASC
OFFSET 10 ROWS
FETCH NEXT 20 ROWS ONLY

Variable values

Instead of a constant number, the values used for OFFSET and FETCH can be variable:

DECLARE @SkipRows INT = 10,
        @FetchRows INT = 20; 

-- Skip the first 10 rows, and return
-- the next 20 rows only
SELECT Id 
FROM OffSetDemo
ORDER BY Id ASC
OFFSET @SkipRows ROWS
FETCH NEXT @FetchRows ROWS ONLY

Expression values

Expressions can also be used to calculate the OFFSET and FETCH values:

DECLARE @PageNumber INT = 5,
        @PageSize INT = 20; 

-- In this example, skip 100 records
-- and return the next 20
SELECT Id 
FROM OffSetDemo
ORDER BY Id ASC
OFFSET @PageNumber * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY

This makes paging on the dataset very easy.

Subquery values

Finally, the values can also be retrieved using a subquery

-- Skip 10 rows and return 
-- the next X rows, determined
-- but a value in the AppSettings table
SELECT Id 
FROM OffSetDemo
ORDER BY Id ASC
OFFSET 10 ROWS
FETCH NEXT (
	SELECT CONVERT(INT, [VALUE]) 
	FROM AppSettings WHERE [Key] = 'PageSize'
	) ROWS ONLY

Notes

The OFFSET + FETCH combination is a very easy, effective way and simple to add paging to a dataset, and offers a better alterative to having to use ROW_NUMBER to add paging.

References

SQL SELECT - ORDER BY Clause

Daily Drop 33: 17-03-2022

At the start of 2022 I set myself the goal of learning one new coding related piece of knowledge a day.
It could be anything - some.NET / C# functionality I wasn't aware of, a design practice, a cool new coding technique, or just something I find interesting. It could be something I knew at one point but had forgotten, or something completely new, which I may or may never actually use.

The Daily Drop is a record of these pieces of knowledge - writing about and summarizing them helps re-enforce the information for myself, as well as potentially helps others learn something new as well.