CTE排序分页后执行Join导致排序失效的原因咨询

核心原因：SQL的顺序保证规则 #

SQL标准明确规定：只有最终SELECT语句中的ORDER BY才能保证返回结果集的顺序。子查询、CTE或视图中的ORDER BY如果仅配合OFFSET/FETCH使用，它的作用只是确定分页要截取的行范围，并不会强制后续的Join、Apply等操作保留这个临时顺序。

SQL Server的查询优化器会根据数据分布、索引情况等选择最优执行计划，Join操作（比如你用到的LEFT JOIN [Order].[OrderItem]和CROSS APPLY _count）会让优化器重新组织数据的处理逻辑——此时优化器的核心目标是提升查询性能，而非保留子查询的临时排序，这就是你看到排序失效的根本原因。

为什么问题随机出现？ #

当数据量较小、或者执行计划刚好“巧合”沿用了子查询的顺序时，结果看起来是正确的，但这属于不可靠的偶然现象。一旦数据量变化、索引更新或者统计信息变更，优化器就可能选择不同的执行计划，排序顺序就会被打乱。

正确解决方案 #

不需要执行两次排序，只需把排序逻辑移到最外层SELECT语句末尾，内层子查询仅负责分页筛选即可：

WITH _data AS 
(
    SELECT 
        O.*
    FROM 
        [Order].[Order] O
    WHERE 
        -- 复杂筛选条件
), 
_count AS 
(
    SELECT COUNT_BIG(0) AS TotalCount 
    FROM _data
)
SELECT 
    _orders.*,
    OI.Id, 
    OI.Amount as 'AmountDecimal', 
    T.TotalCount as 'TotalCount' 
FROM 
    (SELECT * 
     FROM _data 
     ORDER BY
         CASE WHEN @SortByColumnName = 0 AND @SortOrder = 1
                  THEN _data.CreatedOn 
              ELSE '' 
         END ASC,
         CASE WHEN @SortByColumnName = 0 AND @SortOrder <> 1
                  THEN _data.CreatedOn 
              ELSE '' 
         END DESC
         -- 其他排序规则
         OFFSET ((@Page - 1) * @Count) ROWS 
             FETCH NEXT @Count ROWS ONLY
    ) _orders
LEFT JOIN 
    [Order].[OrderItem] OI ON _orders.Id = OI.OrderId
CROSS APPLY 
    _count T
-- 将排序逻辑移至此处，确保最终结果顺序稳定
ORDER BY
    CASE WHEN @SortByColumnName = 0 AND @SortOrder = 1
             THEN _orders.CreatedOn 
         ELSE '' 
    END ASC,
    CASE WHEN @SortByColumnName = 0 AND @SortOrder <> 1
             THEN _orders.CreatedOn 
         ELSE '' 
    END DESC
-- 其他排序规则

补充说明 #

你使用的SQL Server 2017（RTM-CU25）版本的优化器完全遵循上述规则，不存在“子查询排序自动保留到外层”的特殊行为。所有依赖子查询/CTE排序来保证最终结果顺序的写法均属于未定义行为，绝对不能依赖。

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