SQL SERVER : OFFSET and FETCH example

In this article I am going to explain OFFSET and FETCH in SQL Server.

OFFSET and FETCH are introduced in SQL Server 2012. These are used with Select and Order By clause to provide the result set.

OFFSET can only be used with Order By clause.  OFFSET specifies the number of rows to be exclude from the query. Before use the OFFSET make sure value must be equal to zero or greater than otherwise it will give error. Values can’t be negative.

FETCH is optional. FETCH specifies the number of rows to return after the OFFSET. Similar to OFFSET, values can’t be negative. Value should be equal to zero or greater than otherwise it will give error.

 Syntax

Select * from table_name 
order by column_name [ASC |DESC] 
OFFSET number_of_rows_to_skip 
FETCH {FIRST | NEXT} number_of_rows_to_return  {ROW | ROWS} ONLY

 

I am using Northwind database and getting data from Customers table. This table contains approx 90-91 records.

Select count(*) as [number of records] from Customers

 

Example

I want to exclude the first 80 rows and show the all rest records.

Select * from Customers order by CustomerID 
offset 80 rows

 

Output:

 

If now I want to exclude the first 80 rows and get the next 5 records. In this case we have to use the FETCH.

Select * from Customers order by CustomerID 
offset 80 rows 
Fetch next 5 rows only

 

Output: