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