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

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 in sequence based on 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)

SQL Recursive CTE

SQL Server Create Schema

SQL Server Synonyms

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 2,927 total views,  1 views today