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.
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:-
NOTE:-
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
|
No comments:
Post a Comment