OFFSET-FETCH IN SQL SEVER 2012

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

Difference between TOP and OFFSET- FETCH Is OFFSET-FETCH it skippes the rows.In TOP Option it will not skip.

The OFFSET-FETCH option is rigth after the order by . The order by is required when OFFSET-FETCH option includes.

The OFFSET-FETCH option intorduced in SQL-SERVER 2012.

We can use OFFSET-FECTH option 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:

1. Here Offset 0 rows means it will skips the 0 rows.

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

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

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

3. Here Skips the first 5 rows .fetch next 5 rows only means ,it takes 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

Exceute Stored procedure:

exec Get_Employee 5,1

Here 5 is page size , 1 is page number. it will return first 5 rows.

exec Get_Employee 5,2

Here 5 is page size , 2 is page number. it will skips the first 5 rows taking the next 5 record(6-10).