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

Introduction

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:

-- Example of inefficient paging query
WITH NumberedRows AS (
    SELECT
        ROW_NUMBER() OVER(ORDER BY ColumnName) AS RowNum,
        *
    FROM
        TableName
)
SELECT * 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:

-- Example of optimized paging query using OFFSET FETCH
SELECT Column1, Column2
FROM TableName
ORDER BY ColumnName
OFFSET 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.

Additional Information

There is no additional info for this rule.

Example Test SQL

SELECT
  BusinessEntityID
  ,PersonType
 ,FirstName + ' ' + MiddleName + ' ' + LastName
FROM Person.Person
 ORDER BY BusinessEntityID ASC
  OFFSET 100 ROWS
  FETCH NEXT 5 ROWS ONLY

;WITH Paging_CTE AS
(
SELECT
TransactionID
, ProductID
, TransactionDate
, Quantity
, ActualCost
, ROW_NUMBER() OVER (ORDER BY TransactionDate DESC) AS RowNumber
FROM
Production.TransactionHistory
)
SELECT
TransactionID
, ProductID
, TransactionDate
, Quantity
, ActualCost
FROM
Paging_CTE
WHERE RowNumber > 0 AND RowNumber <= 20

SELECT TransactionID
, ProductID
, TransactionDate
, Quantity
, ActualCost
FROM (
    SELECT
    TransactionID
    , ProductID
    , TransactionDate
    , Quantity
    , ActualCost
    , ROW_NUMBER() OVER (ORDER BY TransactionDate DESC) AS RowNumber
    FROM
    Production.TransactionHistory
) AS MyDerivedTable
WHERE 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

Analysis Rules