为何SQL函数在不同服务器上间歇性返回‘子查询返回多个值’错误？

问题现象 #

同一自定义标量函数在不同SQL Server服务器上执行表现不一致：部分服务器正常返回结果，部分服务器持续抛出如下错误：

Msg 512, Level 16, State 1, Line 16
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

最简复现代码（以sys.objects为例，任意多行表均可触发）：

CREATE FUNCTION [dbo].[SubQueryTest] ()
RETURNS BIT
as
BEGIN
    DECLARE @allow bit
    SET @allow=0

    SELECT @allow=1
    FROM sys.objects

    RETURN @allow
END
GO

SELECT dbo.SubQueryTest()
GO

DROP FUNCTION dbo.SubQueryTest
GO

报错服务器的SQL Server版本：Microsoft SQL Server 2019 (RTM-GDR) (KB5035434) - 15.0.2110.4 (X64)

根因分析 #

该现象由**SET ANSI_WARNINGS配置差异**导致：

• 当ANSI_WARNINGS设为ON时，SQL Server会严格校验标量赋值操作：即便SELECT @变量=值的查询最终会用最后一行值覆盖变量，只要返回多行就触发512错误。
• 当ANSI_WARNINGS设为OFF时，SQL Server会静默处理多行赋值，直接取最后一行值赋值给变量，不会报错。

不同服务器的默认配置或会话级配置不一致，导致了执行结果的差异。

解决方案 #

方案1：修改函数逻辑（推荐） #

避免依赖静默多行赋值的行为，明确确保查询返回单行，比如使用TOP 1：

CREATE FUNCTION [dbo].[SubQueryTest] ()
RETURNS BIT
as
BEGIN
    DECLARE @allow bit
    SET @allow=0

    -- 使用TOP 1确保仅返回一行
    SELECT TOP 1 @allow=1
    FROM sys.objects

    RETURN @allow
END
GO

方案2：调整会话/服务器级配置 #

若必须保留原有逻辑，可在执行函数前临时修改会话配置：

SET ANSI_WARNINGS OFF;
SELECT dbo.SubQueryTest();
SET ANSI_WARNINGS ON; -- 执行后恢复默认配置，避免影响其他操作

也可修改服务器级默认配置（不推荐，会影响所有数据库会话）：

sp_configure 'user options', 0; -- 清除现有用户选项
RECONFIGURE;
sp_configure 'user options', 1024; -- 设置ANSI_WARNINGS为OFF（对应位值1024）
RECONFIGURE;

注意事项 #

• ANSI_WARNINGS是SQL Server核心一致性配置，设为OFF可能引发其他静默错误（如空值聚合、字符串截断等），因此优先推荐修改函数逻辑。
• 标量函数性能较差，若业务允许，建议改用表值函数或直接改写为查询语句，提升执行效率。

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