SQL Server 2017使用临时表关联时的Always Encrypted问题

问题原因 #

这是SQL Server 2017版本中Always Encrypted功能的一个查询解析限制：当查询包含临时表与永久加密表的关联逻辑时，SQL Server的参数加密推导逻辑会被打断。临时表属于tempdb，查询优化器处理这类跨数据库（用户库与tempdb）的关联查询时，无法正确将WHERE子句中的@SSN参数与Patients表中加密的SSN列关联，导致sp_describe_parameter_encryption无法识别该参数需要加密。

而表变量的元数据在查询编译阶段就能被完整解析，不会触发临时表的特殊处理流程，参数加密推导可正常工作；移除临时表关联后，查询仅涉及用户库内的加密表，推导逻辑自然也能正常执行。

解决办法 #

1. 改用表变量替代临时表 #

如你已测试的方案，将临时表替换为表变量即可绕过该限制，参数加密状态能被正确识别：

exec sp_describe_parameter_encryption
    N'
        DECLARE @AvailablePatients TABLE (
            PatientID INT NOT NULL
            PRIMARY KEY (PatientID)
        )
        SELECT [SSN], Patients.[FirstName], Patients.[LastName], [BirthDate] 
        FROM Patients
        INNER JOIN @AvailablePatients AS AvailablePatients ON AvailablePatients.PatientID = Patients.PatientID
        WHERE SSN=@SSN',
    N'@SSN char(11)'

2. 将临时表创建与查询拆分到不同批次 #

把临时表的创建语句和后续查询拆分为独立执行批次（用GO分隔），解析查询时临时表已存在于tempdb中，查询优化器能正确推导参数加密状态：

CREATE TABLE #AvailablePatients (
    PatientID INT NOT NULL
    PRIMARY KEY (PatientID)
)
GO
exec sp_describe_parameter_encryption
    N'
        SELECT [SSN], Patients.[FirstName], Patients.[LastName], [BirthDate] 
        FROM Patients
        INNER JOIN #AvailablePatients ON #AvailablePatients.PatientID = Patients.PatientID
        WHERE SSN=@SSN
        DROP TABLE #AvailablePatients',
    N'@SSN char(11)'

3. 升级SQL Server版本 #

微软在SQL Server 2019及后续版本中修复了Always Encrypted在临时表场景下的参数解析缺陷，升级到更高版本可彻底解决该问题。

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