SQL Server OFFSET FETCH clauses are used to set the limit to number of rows returned by a query.
OFFSET and FETCH clause are always used with Order By clause. It is an optional to use with order by clause. Following are the definition of OFFSET and FETCH clause.
OFFSET – It specifies the number of rows to skip before it starts to return rows from the query expression. It is mandatory.
FETCH – It specifies the number of rows to return after the OFFSET clause has been processed. It is an optional.
SYNTAX
SELECTย col1, col2, .... FROM table_name WHEREย conditions ORDER BY column_list [ASC |DESC] OFFSET offset_row_count {ROW | ROWS} FETCH {FIRST | NEXT} fetch_row_count {ROW | ROWS} ONLY
Lets take an example of using OFFSET and FETCH clause with order by clause
Here we have a sample table named as student
Create table Student (StudId int identity(1,1), StudName varchar(30), StudCode varchar(30)) insert into student values ('Rozer M', 'CodrozX3') insert into student values ('Mahesh Singh', 'RodMSzG5') insert into student values ('Sanjay Ak', 'SodAkzV4') insert into student values ('Jack Peter', 'Jodrohn9') insert into student values ('Suraj Rawat', 'Sodroag5') insert into student values ('Munni M', 'Modfobj7') insert into student values ('Honey',' Hodrihy9') insert into student values ('Rahul Singh', 'Rodrihyd6') insert into student values ('Mahesh uniyal', 'ModfihXFT') insert into student values ('Nitesh Kumar', 'Nodtihu3') insert into student values ('Munish Singh' ,'Muonirih56')
Lets fetch the student records from table and sort them in ascending order based on StudId column.
select * from student order by studIdย ย
As you can see, now student records are sorted in ascending order by Studid.
Use of OFFSET clauseย
If you want to skip first 4 records from above query result set then you can use offset clause as given below.
select * from Student order by studId OFFSET 4 ROWS
As you can see, it returns student records by skipping first 4 records from query result set.
Use of FETCH clauseย
If you want to skip first 4 rows and select next 5 rows then, you use FETCH clause with OFFSET clause as shown in following query.
SELECT * FROM Student ORDER BY ย studId
OFFSET 4 ROWS FETCH NEXT 5 ROWS ONLY
As you see, it skipped first 4 rows from query result set and select next 5 rows only.
Also Read..
SQL Server CTE (Common Table Expression)
