OFFSET-FETCH Clause in SQL Server

Rakesh Kalluri
9y
5.4k
0
0

Article

The OFFSET-FETCH option is a filltering option that, like top, you can use to filter the data based on the number of rows.

The difference between top and OFFSET-FETCH is that OFFSET-FETCH skips the rows. The top option does not skip.The OFFSET-FETCH option is effective right after the order by. The order by is required when the offset-fetch option is included. The OFFSET-FETCH option was introduced in SQL Server 2012.
We can use the offset-fecth option to implement pagenation.

--create Employee table
create table Employee
(
EmpId int identity(1,1) primary key,
FirstName varchar(100),
LastName varchar(100),
JoinDate datetime ,
Salary int ,
Department varchar(20)
)

--Insert data to Employee table
insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Rakesh','Kalluri','2012-07-01 10:00:00.000',20000,'Software')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Shabari','Vempati','2011-05-01 10:00:00.000',25000,'Software')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Venkatesh','Bodupaly','2013-04-01 10:00:00.000',15000,'Bpo')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Surjan','Peddineni','2011-07-01 10:00:00.000',25000,'Software')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Nani','Ch','2010-07-01 10:00:00.000',50000,'Software')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Raju','Chinna','2012-07-01 10:00:00.000',25000,'Software')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Kiran','Kumar','2011-07-01 10:00:00.000',20000,'Software')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Raki','Kumar','2012-07-01 10:00:00.000',17000,'Bpo')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Sri','Vidya','2011-07-01 10:00:00.000',30000,'Software')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Fehad','MD','2013-07-01 10:00:00.000',20000,'Bpo')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Anusha','Kumari','2011-07-01 10:00:00.000',35000,'Software')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Venky','Naidu','2013-07-01 10:00:00.000',20000,'Bpo')
insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Radha','Kumari','2012-07-01 10:00:00.000',10000,'Bpo')

select * from Employee

Examples

Here Offset 0 rows means it will skip 0 rows.

select * from Employee order by EmpId asc offset 0 rows;

Here Offset 5 rows means it skips the first 5 rows based on the order by and EmpId asc returns the remianing rows.

select * from Employee order by EmpId asc offset 5 rows;

The following skips the first 5 rows. It fetches the next 5 rows, in other words it takes the next five rows ony.

select * from Employee order by EmpId asc offset 5 rows fetch next 5 rows only;

Using OFFSET-FECTH pagenation

create procedure Get_Employee
(@pagesize int ,@pagenum int )
as
begin
select * from Employee order by EmpId asc offset (@pagenum-1)*@pagesize rows fetch next @pagesize rows only;
end