Sql server 2012 OFFSET use

Hello,

Today i want to share sql server 2012 new features OFFSET Fetch next example.

It is working fast more than our row_number function. It will show you difference when data in lacs and your query is complex like use inner join more than two tables and also use some sub querys.

Use below query to get more idea for fetch next function.

DECLARE @tblTemp TABLE   
(   
    ID INT,  
    firstName NVARCHAR(100),
    lastName NVARCHAR(100)
);
  
INSERT INTO @tblTemp VALUES(1,'Shikhar', 'Dhawan')  
INSERT INTO @tblTemp VALUES(2,'Rohit', 'Sharma')  
INSERT INTO @tblTemp VALUES(3,'Kumar', 'Sangakara')  
INSERT INTO @tblTemp VALUES(4,'Jonathan', 'Trott')  
INSERT INTO @tblTemp VALUES(5,'Virat', 'Kohli')
INSERT INTO @tblTemp VALUES(6,'Ms', 'Dhoni')
INSERT INTO @tblTemp VALUES(7,'Ravindra', 'Jadeja')
INSERT INTO @tblTemp VALUES(8,'Ravichandran', 'Ashiwn')
INSERT INTO @tblTemp VALUES(9,'Mitchell', 'Mccleanaghan')
INSERT INTO @tblTemp VALUES(10,'James', 'Anderson')
INSERT INTO @tblTemp VALUES(11,'Ishant', 'Sharma')
INSERT INTO @tblTemp VALUES(12,'Joe', 'Root')

DECLARE @pageIndex INT = 0,
	@pageSize INT = 5,
	@sortField NVARCHAR(50) = 'firstName',
	@order BIT = CAST(1 AS BIT)

SELECT * FROM @tblTemp
ORDER BY 
	CASE WHEN (@sortField ='firstName' AND  @order=CAST(1 AS BIT)) THEN firstName END ASC,
	CASE WHEN (@sortField ='firstName' AND @order=CAST(0 AS BIT)) THEN firstName END DESC,
	CASE WHEN (@sortField ='lastName' AND @order=CAST(1 AS BIT)) THEN lastName END ASC,
	CASE WHEN (@sortField ='lastName' AND @order=CAST(0 AS BIT)) THEN lastName END DESC
OFFSET (@pageIndex * @pageSize) ROWS
FETCH NEXT @pageSize ROWS ONLY

Remember Fetch next will only work with order by clause. You have to set order by to work with offset and fetch next.

Here in this query i cover multiple order issue of offset fetch next. If you want to use dynamic sortfield in offset than use case statement in order by clause.

enjoy new feature of sql server 2012.

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>