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 in SQL Server to specify how many rows returned to the client. This feature can be used since SQL Server 2012.

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

OFFSET and FETCH Examples in SQL Server

Example1

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

Example2

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

Example3

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.

SQL Server Paging Using Offset and Fetch

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.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *