Feeds:
Posts
Comments

Posts Tagged ‘Offset Rows’

“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

limitrows1.1

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

limitrows1.2-1

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

limitrows1.4

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.

S.No

Type

Old Approach

New Approach

1

Common Table Expression

Yes (required)

N/A

2

ROW_NUMBER

Yes (required)

N/A

3

CPU time

15 ms

15 ms

4

Elapsed time

1387 ms

375 ms

5

Query Cost (relative to the batch)

53%

47%

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.

Read Full Post »