Open In App

Offset-Fetch in MS SQL Server

Improve
Improve
Like Article
Like
Save
Share
Report

Suppose a table has 30 rows. A user wants to extract list of last 10 rows and skip topmost rows. To make job easier, the offset-fetch clause is used in the query.

Syntax :

order by column_list[ASC|DESC]
Offset offset_row count{ROW|ROWS}
Fetch{FIRST|NEXT} fetch row_count {ROW|ROWS} only


Analysis of Syntax :

  • Order by clause is mandatory to use alongside offset-fetch clause otherwise it results in an error. ASC arranges rows from ascending to descending order while DESC arranges rows from descending to ascending order.
  • Offset skips number of rows specified in a table.
  • Fetch returns number of rows after offset clause is being used. It returns specified number of rows.
  • FIRST returns number of rows at start of table after offset is while NEXT returns consecutive rows after first set of rows.

Example –


Table – Student

Roll number Name Course
111 Riya CSE
112 Apoorva ECE
113 Mina Mech
114 Rita Biotechnology
115 Veena Chemical
116 Deepa EEE

If the user wants to skip first two rows and return rest of them, query is given as –

select name, rollnumber, course
from student 
order by rollnumber ASC
offset 2 ROWS

Output –

Roll number Name Course
113 Mina Mech
114 Rita Biotechnology
115 Veena Chemical
116 Deepa EEE

Offset skips number of rows specified in query while order by ASC arranges rows from ascending to descending order. If a user wants to skip first 6 rows and fetch the next ones, query is given as –

select name, rollnumber, course 
from student
order by roll number ASC
offset 6 ROWS
fetch FIRST 2 ROWS ONLY

Output –

Roll number Name Course
117 Vani Mech
118 Megha ECE

Offset clause skips all rows specified in table while Fetch clause returns first two rows after offset clause. In the fetch clause, FIRST and NEXT can be used according to user’s requirements. Offset clause is mandatory to use while fetch is optional for use in queries.


Last Updated : 29 Jul, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads