SA0171 : The ROW_NUMBER paging pattern can be replaced with OFFSET FETCH clause

The topic describes the SA0171 analysis rule.

Message

The ROW_NUMBER paging pattern can be replaced with OFFSET FETCH clause

Description

The rule checks for usage of the ROW_NUMBER() pattern for paging.

How to fix

Rewrite the query to use the OFFSET FETCH clause that introduced in SQL Server 2012. For version of SQL Server prior to 2012 the rule can be suppressed.

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
8 minutes per issue.
Categories

Design Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1SELECT 
 2  BusinessEntityID
 3  ,PersonType
 4 ,FirstName + ' ' + MiddleName + ' ' + LastName 
 5FROM Person.Person
 6 ORDER BY BusinessEntityID ASC
 7  OFFSET 100 ROWS 
 8  FETCH NEXT 5 ROWS ONLY
 9
10;WITH Paging_CTE AS
11(
12SELECT
13TransactionID
14, ProductID
15, TransactionDate
16, Quantity
17, ActualCost
18, ROW_NUMBER() OVER (ORDER BY TransactionDate DESC) AS RowNumber
19FROM
20Production.TransactionHistory
21)
22SELECT
23TransactionID
24, ProductID
25, TransactionDate
26, Quantity
27, ActualCost
28FROM
29Paging_CTE
30WHERE RowNumber > 0 AND RowNumber <= 20
31
32SELECT TransactionID
33, ProductID
34, TransactionDate
35, Quantity
36, ActualCost
37FROM (
38    SELECT
39    TransactionID
40    , ProductID
41    , TransactionDate
42    , Quantity
43    , ActualCost
44    , ROW_NUMBER() OVER (ORDER BY TransactionDate DESC) AS RowNumber
45    FROM
46    Production.TransactionHistory
47) AS MyDerivedTable
48WHERE MyDerivedTable.RowNumber BETWEEN 0 AND 20
Analysis Results
  Message Line Column
1 SA0171 : The ROW_NUMBER paging pattern can be replaced with OFFSET FETCH clause. 18 2
2 SA0171 : The ROW_NUMBER paging pattern can be replaced with OFFSET FETCH clause. 44 6
See Also

Other Resources

Analysis Rules