如何避免SQL Server重复执行同一子查询

现有如下结构的SQL查询：

SELECT * 
FROM 
    ... <generated code> ...
    (SELECT <fields>,  
        CASE(SELECT TOP 1 ID FROM [Configuration] WHERE IsDefault=1 ORDER BY ID)
            WHEN 1 THEN t.FirstName
            WHEN 2 THEN t.LastName END As Identifier
     FROM <table> t) AS tmp
... <generated code> ...
WHERE <generated filters>

查询执行计划显示，Configuration表上的Clustered Index Scan执行次数与<table>表的行数一致，但该子查询的结果固定不变。手动将子查询替换为当前配置值后，查询运行速度明显提升。

由于查询首尾部分由应用代码自动生成，无法手动修改。我尝试创建确定性函数封装该子查询，期望SQL Server识别到函数结果固定、不依赖当前行，只需执行一次，但实际函数仍被多次执行，优化未达预期。

请问该如何优化此查询？我是否误解了确定性函数的用法？或是函数创建有误？还有其他解决方法吗？

一、确定性函数失效的原因 #

SQL Server的确定性函数指输入相同则输出必然相同的函数，但你的函数依赖Configuration表的数据（即便数据固定），这类函数默认属于非确定性函数——SQL Server无法保证表数据在查询执行过程中不会被修改，因此仍会逐行执行。

即便给函数加上WITH SCHEMABINDING，只要内部涉及表查询，它依然是不确定性函数，SQL Server不会将其结果视为常量缓存。

二、可行优化方案 #

1. 函数+CROSS JOIN强制常量求值 #

若坚持用函数，可创建带SCHEMABINDING的函数，再通过CROSS JOIN将函数结果作为常量引入查询，避免重复执行：

CREATE FUNCTION dbo.GetDefaultConfigID()
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    RETURN (SELECT TOP 1 ID FROM dbo.Configuration WHERE IsDefault=1 ORDER BY ID)
END

修改子查询部分为：

SELECT <fields>,  
    CASE cfg.DefaultID
        WHEN 1 THEN t.FirstName
        WHEN 2 THEN t.LastName END As Identifier
FROM <table> t
CROSS JOIN (SELECT dbo.GetDefaultConfigID() AS DefaultID) cfg

这种方式会让SQL Server先计算一次函数结果，再关联主表，消除重复执行。

2. 提前缓存配置值到变量 #

若应用允许在生成的查询外层添加逻辑，可先将配置值存入变量，再在子查询中引用：

DECLARE @DefaultID INT = (SELECT TOP 1 ID FROM [Configuration] WHERE IsDefault=1 ORDER BY ID)

SELECT * 
FROM 
    ... <generated code> ...
    (SELECT <fields>,  
        CASE @DefaultID
            WHEN 1 THEN t.FirstName
            WHEN 2 THEN t.LastName END As Identifier
     FROM <table> t) AS tmp
... <generated code> ...
WHERE <generated filters>

3. 给Configuration表加索引 #

若无法修改查询结构，可给Configuration表的IsDefault字段创建非聚集索引，包含ID列，降低重复扫描的开销：

CREATE NONCLUSTERED INDEX IX_Configuration_IsDefault ON dbo.Configuration(IsDefault) INCLUDE (ID)

4. 用视图封装优化后的逻辑 #

创建视图封装包含Identifier的查询，确保内部先获取常量配置值再关联主表：

CREATE VIEW dbo.TableWithIdentifier
WITH SCHEMABINDING
AS
SELECT 
    t.<fields>,
    CASE cfg.DefaultID
        WHEN 1 THEN t.FirstName
        WHEN 2 THEN t.LastName END As Identifier
FROM dbo.<table> t
CROSS JOIN (SELECT TOP 1 ID AS DefaultID FROM dbo.Configuration WHERE IsDefault=1 ORDER BY ID) cfg

让应用生成的查询直接引用该视图，SQL Server会自动优化视图内部逻辑，只执行一次配置查询。

三、总结 #

你之前的核心问题是误解了确定性函数的适用场景——依赖表数据的函数无法被SQL Server视为常量。优先推荐CROSS JOIN引入常量值或创建优化视图的方案；若无法修改查询结构，给Configuration表加索引是最直接的补救措施。

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