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

The misuse of ROW_NUMBER() for paging can lead to inefficient query performance.

Description

The ROW_NUMBER() function is often used to assign a unique sequential integer to rows within a result set. However, its use in implementing pagination can be problematic, especially in large datasets typical in SQL Server environments.

For example:

SQL
Copy
1-- Example of inefficient paging query
2WITH NumberedRows AS (
3    SELECT 
4        ROW_NUMBER() OVER(ORDER BY ColumnName) AS RowNum,
5        * 
6    FROM 
7        TableName
8)
9SELECT * FROM NumberedRows WHERE RowNum BETWEEN 1001 AND 2000;

This approach can result in poor performance as the ROW_NUMBER() function calculates numbers for all rows before filtering, which means it processes more data than necessary. Additionally, SQL Server may not optimize these queries as efficiently as others.

  • Increased resource usage due to scanning all rows to assign numbers.

  • Poor query performance when dealing with large datasets.

How to fix

Optimize paging queries by using the OFFSET FETCH clause instead of ROW_NUMBER() to improve performance with large datasets in SQL Server 2012 and later.

Follow these steps to address the issue:

  1. Identify queries using ROW_NUMBER() for pagination, such as those enclosing it in a common table expression (CTE) or subquery and filtering with BETWEEN.

  2. Rewrite the query using the OFFSET FETCH clause. This approach allows the server to efficiently skip rows and directly fetch the desired range.

  3. Validate the revised query’s performance improvement by comparing execution plans before and after the change. Use SQL Server Management Studio (SSMS) to analyze execution plans.

For example:

SQL
Copy
1-- Example of optimized paging query using OFFSET FETCH
2SELECT Column1, Column2 
3FROM TableName 
4ORDER BY ColumnName
5OFFSET 1000 ROWS FETCH NEXT 1000 ROWS ONLY;

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