SQL Server不同版本含DISTINCT的分页查询排序差异问题

我编写了如下分页查询语句：

DECLARE @StartRow BIGINT = 1;
DECLARE @LastRow BIGINT = 50;

WITH PagedRowSet AS (
    SELECT Field1, Field2, ...,
        ROW_NUMBER() OVER (ORDER BY SortField1 ASC) AS 'RowNum' 
    FROM View1
    WHERE ...
)
SELECT DISTINCT Field1, Field2, ...,
    (SELECT MAX(RowNum) FROM PagedRowSet) AS 'TotalRows'
FROM PagedRowSet
WHERE RowNum BETWEEN @StartRow AND @LastRow;

该语句在生产环境的SQL Server 2012中能按PagedRowSet内的ORDER BY正常排序，但在本地SQL Server 2014中，必须移除DISTINCT才能正常排序。请问这是什么原因？是单纯的版本差异还是有相关数据库设置需要检查？

核心原因：优化器行为变化+原写法的逻辑漏洞 #

1. SQL Server 2012的偶然行为：2012的查询优化器在处理带DISTINCT的外层查询时，碰巧保留了CTE中ROW_NUMBER()生成的排序顺序，但这并非合规结果——SQL标准明确规定：没有显式ORDER BY的SELECT语句，结果集的输出顺序是不保证的，2012的表现只是优化器执行计划的巧合，不能作为稳定依赖。
2. SQL Server 2014的标准合规调整：2014的查询优化器做了改进，更严格遵循SQL规范。当外层使用DISTINCT时，优化器会优先执行去重逻辑，这会直接打乱CTE中原本基于RowNum的排序——因为DISTINCT会重新组织结果集，没有显式排序指令的话，输出顺序完全由优化器自主决定。

原写法的关键问题 #

你在CTE中生成了基于SortField1排序的RowNum，但外层用DISTINCT对Field1,Field2...去重时，RowNum并未包含在去重的字段列表里，这会让优化器忽略原有的排序逻辑，最终输出顺序完全不可控。

修正方案 #

方案1：将DISTINCT移至CTE内部（推荐）

先对源数据完成去重，再生成排序用的RowNum，这样分页时的排序逻辑会稳定可靠：

DECLARE @StartRow BIGINT = 1;
DECLARE @LastRow BIGINT = 50;

WITH PagedRowSet AS (
    SELECT DISTINCT Field1, Field2, ...,
        ROW_NUMBER() OVER (ORDER BY SortField1 ASC) AS 'RowNum' 
    FROM View1
    WHERE ...
)
SELECT Field1, Field2, ...,
    (SELECT MAX(RowNum) FROM PagedRowSet) AS 'TotalRows'
FROM PagedRowSet
WHERE RowNum BETWEEN @StartRow AND @LastRow;

方案2：外层显式添加ORDER BY

如果必须在生成RowNum后去重，一定要在外层查询显式指定排序字段，强制优化器按预期顺序输出：

DECLARE @StartRow BIGINT = 1;
DECLARE @LastRow BIGINT = 50;

WITH PagedRowSet AS (
    SELECT Field1, Field2, ...,
        ROW_NUMBER() OVER (ORDER BY SortField1 ASC) AS 'RowNum' 
    FROM View1
    WHERE ...
)
SELECT DISTINCT Field1, Field2, ...,
    (SELECT MAX(RowNum) FROM PagedRowSet) AS 'TotalRows'
FROM PagedRowSet
WHERE RowNum BETWEEN @StartRow AND @LastRow
ORDER BY SortField1 ASC; -- 显式指定排序字段

总结 #

这不是单纯的版本差异，而是SQL Server优化器对SQL标准的遵循程度提升导致的。你的原写法依赖了不稳定的优化器行为，属于逻辑漏洞，建议采用上述两种方案修正，避免在不同版本或环境中出现不一致的结果。

内容的提问来源于stack exchange，提问作者NobleGuy