SQL Server 2022函数中ORDER BY位置索引报错问题咨询

在SQL Server 2016中正常运行的自定义标量函数，因使用ORDER BY位置索引而非列名，在SQL Server 2022执行时触发报错：

The ORDER BY position number 2 is out of range of the number of items in the select list

但将函数内的代码单独作为脚本运行却能正常执行，具体示例如下：

创建函数代码 #

create FUNCTION [dbo].[TestingOrderBy]() returns INT
  as
  begin
    declare @VR int, @Order int

    select top 1
    @VR = [version_revision],
    @Order = case when L.[id] > 1000 then 1 else 99 end
   from msdb.[dbo].[msdb_version] L
    order by 2 asc, L.[version_major] desc

    return @VR
  end

执行函数触发错误 #

select dbo.TestingOrderBy()

单独运行函数内逻辑（正常执行） #

declare @VR int, @Order int

select top 1
@VR = [version_revision],
@Order = case when L.[id] > 1000 then 1 else 99 end
from msdb.[dbo].[msdb_version] L
order by 2 asc, L.[version_major] desc

select @VR

这是SQL Server 2022对标量值函数的查询优化逻辑调整导致的差异：

• 在标量函数上下文里，优化器会分析函数的返回值仅依赖@VR变量，未被后续使用的@Order变量对应的SELECT列表项会被优化器忽略，此时SELECT列表实际只剩1个有效项，ORDER BY 2就会因超出范围报错。
• 而单独运行脚本时，不属于函数的封闭上下文，优化器不会做这种“精简”处理，SELECT列表会完整保留两个赋值项，ORDER BY 2能正常对应到case表达式，因此可以正常执行。

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