OFFSET and FETCH in SQL Server

If you do not want to send the entire query result to the client, you can use OFFSET and FETCH to specify how many rows returned to the client.

I think it can be understood more easily by making an example. Let’s examine the code block below.

In the first query, we select [AdventureWorks2014].[Person].[Address] table sorting by City column.

In the second query, we bring all the records except the first 5 records of the same query.

In the third query, we bring only 10 records starting from the first record. We put the OPTION (MAXDOP 1) query hint at the end of the query so that the query result is the same each time. If we don’t, and the query has parallelism, the sorting can be different each time.

This feature can be used for paging. For example, with the following code, we bring up the fifth page when the number of lines per page is 10.