For an instructor lead, in-depth look at learning SQL click below.
One of the common tasks when working with an SQL database is the pagination of results. OFFSET and FETCH are used in SQL Server 2012 and later versions to achieve this purpose. Let’s dive into understanding how it works.
What is OFFSET and FETCH in SQL?
OFFSET is a keyword in SQL that allows you to skip a certain number of tuples in the result of your query. On the other hand, FETCH in conjunction with OFFSET helps a user to limit the number of tuples.
How to use OFFSET and FETCH in SQL?
Let’s use an example to understand the process of implementing pagination better. Suppose we have a table Users, and we want to list users 11 to 20.
|
1 2 3 4 5 6 7 |
SELECT UserId, UserName, UserEmail FROM Users ORDER BY UserId OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY |
The “OFFSET 10 ROWS” command skips the first 10 rows, and then “FETCH NEXT 10 ROWS ONLY” returns the next 10. Keep in mind that to use OFFSET and FETCH, we need to ORDER BY a certain column. The OFFSET/FETCH row count expression can be any arithmetic, constant, or parameter expression that will return an integer value. The row count expression does not support scalar sub-queries.
Considerations and Limitations
Using OFFSET and FETCH in SQL is pretty straight forward. However, you need to consider the following things:
- ORDER BY is mandatory to use OFFSET and FETCH.
- You should know beforehand how many records you want to skip.
Despite these limitations, OFFSET and FETCH are extremely useful in managing large amounts of data and implementing efficient pagination on the server side, which can lead to performance improvements.
Summary
In summary, OFFSET and FETCH are powerful commands in SQL that allow you to manage and manipulate the result set by skipping and limiting the rows returned by your query. This is especially useful when dealing with large databases where you may need to break down result sets into manageable chunks.