OFFSET AND FETCH

SQL Server 2012 introduces brand new query hints that allow you to implement query paging solution. In the past, we have used TOP operator to return the top # number of rows from a table. However, OFFSET & FETCH query clauses can give you more benefits than just the TOP operator.

OFFSET: Allows you to offset the record pointer to a specific row in a table

FETCH: Allows you to fetch/return number of rows you request in Fetch.

OFFSET and FETCH when used together can return the exact/precise set of data you are looking for.

Let's take few examples to understand these features.

I am creating Employee as base table to use in my examples.

 

IF OBJECT_ID('dbo.Employee', 'U') IS NOT NULL DROP TABLE dbo.Employee
CREATE TABLE dbo.Employee
(
  EmployeeID INT NOT NULL
,EmployeeName NVARCHAR(100) NOT NULL
,BasicSalary INT NOT NULL
,DOB DATE NOT NULL
)
 

INSERT dbo.Employee (EmployeeID, EmployeeName, BasicSalary, DOB)
VALUES
(1, 'Hari', 5000, '1983-05-05')
,(2, 'John', 3500, '1978-10-25')
,(3, 'Peter', 4500, '1982-02-10')
,(4, 'Ravi', 2500, '1980-03-04')
,(5, 'Max', 3600, '1985-09-20')

SELECT EmployeeID
,EmployeeName
,BasicSalary
,DOB
FROM dbo.Employee

 







Use OFFSET to offset the record pointer to a specific row in a table:

SELECT EmployeeID
,EmployeeName
,BasicSalary
,DOB
FROM dbo.Employee
ORDER BY EmployeeID
OFFSET 2 ROWS

Use OFFSET to offset the record pointer to a specific row in a table and use FETCH to pull only required number of rows:

SELECT EmployeeID
,EmployeeName
,BasicSalary
,DOB
FROM dbo.Employee
ORDER BY EmployeeID
OFFSET 2 ROWS
FETCH NEXT 2 ROWS ONLY