How to Use "OFFSET and FETCH" Feature of SQL Server 2012

OFFSET and FETCH is introduced in SQL Server 2012. Most popular uses for OFFSET and FETCH is paging

OFFSET

The OFFSET is used to specify the starting point to return the first set of records or we can say that
OFFSET is used for the number of rows to skip before including them into the result.

Syntax:-

SELECT   COLUMNS1,COLUMNS1,....COLUMNSN
FROM your_TABLE_name
ORDER BY COLUMNS1 OFFSET ROWS-TO-SKIP ROWS

Example:-

SELECT Id, Name, Age, Department
FROM Employee
ORDER BY Id OFFSET 2 ROWS

NOTE:-

• It cannot be used on its own. OFFSET can only be used with an ORDER BY clause
• OFFSET values must be greater or equal to zero. 
• If OFFSET is 0, then no rows are skipped.
• If OFFSET is greater than the number of rows in the ordered results, then no rows are returned.

FETCH NEXT

The FETCH is used to retrieve a set number of rows. FETCH can’t be used on its own,  it is used in conjunction with OFFSET.

SELECT Id, Name,Age,Department FROM Employee
ORDER BY Id OFFSET 2 ROWS
FETCH NEXT 10 ROWS ONLY 

In the following screen you can see OFFSET and FETCH works.  OFFSET is using to skip the first 2 rows and FETCH is then used to retrieve the next 5

Id	Name	Age	Department
1	Alok Kumar Singh	30	IT
2	Tomas Paul	45	IT
3	Cristomfer Dee	85	HR
4	Niel Macengi	55	BPO
5	Rahul Singh	25	IT
6	Manoj Singh	12	IT
7	Adam Zempa	58	BPO
8	Crisno Crief	96	HR
9	David Martin	56	IT
10	Ricky Ponting	21	BPO
11	Poing Cahndda	66	IT
12	Chandan Subhash	46	HR
13	M. Shekh	45	BPO

SELECT Id, Name,Age,Department FROM Employee
ORDER BY Id OFFSET 2 ROWS
FETCH NEXT 10 ROWS ONLY 

Result:-

Id	Name	Age	Department
3	Cristomfer Dee	85	HR
4	Niel Macengi	55	BPO
5	Rahul Singh	25	IT
6	Manoj Singh	12	IT
7	Adam Zempa	58	BPO