SQL Server Portal

“How to implement LIMIT in SQL Server?” (How to pick up certain rows after certain rows in SQL SERVER), I came across this question many times in multiple forums and the common solution is, to use common table expression and ROW_NUMBER. However, this is an old approach. A new approach has been introduced in SQL Server 2012 using OFFSET ROWS FETCH NEXT ROWS.

Let me create a sample script to explain both approaches.

USE AdventureWorks2012;
GO
Select
FirstName
,Jobtitle
From [HumanResources].[vEmployee]
Order By FirstName,Jobtitle
--OUTPUT

Lets discuss both approaches with examples :

Old Approach:
In this approach, we will pick 10 rows after row number 10 (meaning row number 11 till 20) from [HumanResources].[vEmployee]. Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012;
GO
;With CTE_Employee_List As
(
Select ROW_NUMBER() Over
(Order By[FirstName],[JobTitle]) As [Row Number],
FirstName
,Jobtitle
From [HumanResources].[vEmployee]
)
Select FirstName,Jobtitle
From CTE_Employee_List
Where [Row Number] Between 11 And 20;
GO
--OUTPUT

New Approach:
In the new approach too, we will select 10 rows after row number 10 (meaning row number 11 till 20) from [HumanResources].[vEmployee] and the script goes as follows :

--This script is compatible with SQL Server 2012 and above.
USE AdventureWorks2012;
GO
Select
FirstName
,Jobtitle
From [HumanResources].[vEmployee]
Order By FirstName,Jobtitle
Offset 10 Rows
Fetch Next 50 Rows Only;
--OUTPUT

As we can see, the output is exactly same but a lot of other parameters are different that can impact the performance.
Given below is the summary of both approaches.

Conclusion :
If you are using SQL Server 2012, I would recommend to use the new approach. It not only boosts the performance but reduce the line of code as well.