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.
|
1 2 3 |
SELECT * FROM [AdventureWorks2014].[Person].[Address] Order By City ASC OPTION(MAXDOP 1) |
Example2
In the second query, we bring all the records except the first 5 records of the same query.
|
1 2 3 4 |
SELECT * FROM [AdventureWorks2014].[Person].[Address] Order By City ASC OFFSET 5 ROWS OPTION(MAXDOP 1); |
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.
|
1 2 3 4 5 |
SELECT * FROM [AdventureWorks2014].[Person].[Address] Order By City ASC OFFSET 40 ROWS FETCH NEXT 10 ROWS ONLY OPTION(MAXDOP 1); |
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.
|
1 2 3 4 5 6 |
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 5 SELECT * FROM [AdventureWorks2014].[Person].[Address] Order By City ASC OFFSET @RowsPerPage * (@PageNumber-1) ROWS FETCH NEXT @RowsPerPage ROWS ONLY OPTION(MAXDOP 1); |
