SQL Server – Limit rows with OFFSET & FETCH

In this SQL tutorial, you’ll learn  how to use the SQL Server’s OFFSET and FETCH clause to limit the number of records that can be returned from the query.

What is OFFSET & FETCH?

OFFSET and FETCH clause are the optional parameters that can be used along with the ORDER BY clause in SQL Server. These two clauses allows you to limit the number of records that needs to be returned when the query is executed.

Syntax

The below syntax demonstrates the usage if the OFFSET and FETCH clauses in SQL Server.

SELECT express
FROM Table
ORDER BY column_list [ASC |DESC]
OFFSET offset_row_count {ROW | ROWS}
FETCH {FIRST | NEXT} fetch_row_count {ROW | ROWS} ONLY

Parameters

• OFFSET
  • The OFFSET clause specifies the number of records to skip before returning the rows. The offset_row_count can be any value that is constant or variable that has a non-negative value.
• FETCH
  • The FETCH clause pecifies the number of records to return after the OFFSET statement is processed. The offset_row_count value for FETCH should be greater than or equals to one.

Note :

1. You should always use the OFFSET and FETCH clause along with the ORDER BY clause. If you try to use it outside of ORDER BY scope, you will receive an error.
2. One of the main use case of the OFFSET and FETCH clause when you want to implement some kind of paging solution. with-in your application.
3. Both of these clauses aare pretty new and is available in SQL Server 2012 and higher versions.

Let’s use the Person table from the AdventureWorks database to explore these two clauses.

The below query returns all he FirstNames from the Person table and sorts by their FirstName.

SELECT FirstName
FROM [Person].[Person] 
ORDER BY FirstName

If you need to skip the first 10 records from the Person table and return the rest, you will need to use the PFFSET clause as shown in the below query.

SELECT FirstName
FROM [Person].[Person] 
ORDER BY FirstName
OFFSET 10 ROWS;

If you need to skip the first 10 records from the Perso Table and select the next 20 person records, you can use both OFFSET and FETCH clause as shown in the below query.

SELECT FirstName
FROM [Person].[Person] 
ORDER BY FirstName
OFFSET 10 ROWS
FETCH NEXT 20 ROWS ONLY;