Sql server 2012 OFFSET use


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.

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

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.