Advertisement

Latest Post

Tuesday, 18 October 2016

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



Government Jobs