Offset-Fetch in MS SQL Server

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.