Click or drag to resize

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.

The OFFSET FETCH Clause introduced in SQL Server 2012 is recommended.

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.

Categories

Design Rules, New Rules

Additional Information

There is no additional info for this rule.

Example Test Script
 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

 MessageLineColumn
1SA0171 : The ROW_NUMBER paging pattern can be replaced with OFFSET FETCH clause.182
2SA0171 : The ROW_NUMBER paging pattern can be replaced with OFFSET FETCH clause.446
See Also

Other Resources