Post
Ask Question

OFFSET-FETCH Clause in SQL Server

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.

  1. --create Employee table  
  2. create table Employee  
  3. (  
  4.     EmpId int identity(1,1) primary key,  
  5.     FirstName varchar(100),  
  6.     LastName varchar(100),  
  7.     JoinDate datetime ,  
  8.     Salary int ,  
  9.     Department varchar(20)  
  10. )  
  1. --Insert data to Employee table  
  2. insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Rakesh','Kalluri','2012-07-01 10:00:00.000',20000,'Software')  
  3. insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Shabari','Vempati','2011-05-01 10:00:00.000',25000,'Software')  
  4. insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Venkatesh','Bodupaly','2013-04-01 10:00:00.000',15000,'Bpo')  
  5. insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Surjan','Peddineni','2011-07-01 10:00:00.000',25000,'Software')  
  6. insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Nani','Ch','2010-07-01 10:00:00.000',50000,'Software')  
  7. insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Raju','Chinna','2012-07-01 10:00:00.000',25000,'Software')  
  8. insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Kiran','Kumar','2011-07-01 10:00:00.000',20000,'Software')  
  9. insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Raki','Kumar','2012-07-01 10:00:00.000',17000,'Bpo')  
  10. insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Sri','Vidya','2011-07-01 10:00:00.000',30000,'Software')  
  11. insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Fehad','MD','2013-07-01 10:00:00.000',20000,'Bpo')  
  12. insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Anusha','Kumari','2011-07-01 10:00:00.000',35000,'Software')  
  13. insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Venky','Naidu','2013-07-01 10:00:00.000',20000,'Bpo')  
  14. insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Radha','Kumari','2012-07-01 10:00:00.000',10000,'Bpo')  
  1. select * from Employee  


Examples

Here Offset 0 rows means it will skip 0 rows.
  1. 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.
  1. 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.
  1. select * from Employee order by EmpId asc offset 5 rows fetch next 5 rows only;  


Using OFFSET-FECTH pagenation
  1. create procedure Get_Employee  
  2. (@pagesize int ,@pagenum int )  
  3. as  
  4. begin  
  5. select * from Employee order by EmpId asc offset (@pagenum-1)*@pagesize rows fetch next @pagesize rows only;  
  6. end  
Exceute Stored Procedure
  1. exec Get_Employee 5,1  
Here 5 is the page size and 1 is the page number. It will return the first 5 rows.


  1. exec Get_Employee 5,2  
Here 5 is the page size and 2 is the page number. It will skip the first 5 rows then take the next 5 record (6-10).


Similar Articles