Home » SQL Server OFFSET FETCH

SQL Server OFFSET FETCH

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')

 

To see the student records , we use select query to get the student records from table and sort them in ascending order based on StudId column.

 select * from student order by studId  

As we can see, we have now student records in sequence based on studid.

Use of OFFSET clause 

If we want to skip first 4 records from above query result set then we will use offset clause as given below

select * from Student order by studId
OFFSET 4 ROWS

As we can see, it returns student records by skipping first 4 records from query result set.

Use of FETCH clause 

If we want to skip first 4  rows and select next 5 rows then , we 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 we see , it skipped first 4 rows from query result set and select next 5 rows only.




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.