SQL Server 2017 上 Always Encrypted的官方 Microsoft 文档指出：
确定性加密总是为任何给定的纯文本值生成相同的加密值。
使用确定性加密允许对加密列进行点查找、等式连接、分组和索引。
（粗体强调我的）
我目前正在使用 SQL Server 2017 RTM-CU17 (KB4515579) v14.0.3238.1 标准版。
我的 SSMS（当前使用 v18.4）连接已配置为Enable Always Encrypted (column encryption)选中复选框，并且Enable Parameterization for Always Encrypted还选中了 Query Options -> Execution -> Advanced 设置。
下面是我拥有的表模式。
EmployeeID和列使用FullName加密Deterministic Encryption Type。
该Temp列使用 加密Randomized Encryption Type。
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmployeeTemperature]
(
[Entry] [int] IDENTITY(1,1) NOT NULL,
[CheckerID] [varchar](26) NOT NULL,
[EmployeeID] [char](10) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
[FullName] [varchar](50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[Temp] [decimal](4, 1) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
[Date] [date] NOT NULL, -- to support Date-CheckerID-FullName unique constraint
[DateTime] [datetime] NOT NULL,
[Station] [smallint] NOT NULL,
[Question1] [bit] NOT NULL,
[Question2] [bit] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE UNIQUE CLUSTERED INDEX [UCI_EmployeeTemperature]
ON [dbo].[EmployeeTemperature]
(
[Date] ASC,
[CheckerID] ASC,
[FullName] ASC
)
WITH
(
PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
GO
以下Stored Procedure代码用于检索加密数据（最终将由 ASPX 单页应用程序 (SPA) Web 应用程序使用）。
--SELECT OBJECT_ID('dbo.sp_GetEmployeeTemps','P') -- debug below
IF OBJECT_ID('dbo.sp_GetEmployeeTemps','P') IS NULL
EXEC('CREATE PROCEDURE [dbo].[sp_GetEmployeeTemps] AS BEGIN SET NOCOUNT ON; END')
GO
ALTER PROCEDURE [dbo].[sp_GetEmployeeTemps]
AS
SELECT
ET.[Entry]
,CASE
WHEN HR.[Employee_ID] IS NOT NULL THEN 'E'
ELSE 'V'
END AS [Visitor] -- Show if record is for Employee or Visitor
,ISNULL(HR.[Name],ET.[FullName]) AS [Name] -- ISNULL for visitor. return visitor's name if not an employee.
,ET.[Temp]
,(SELECT DISTINCT chk.[Name] FROM [dbo].[Checker] AS chk INNER JOIN [dbo].[EmployeeTemperature] ON ET.[CheckerID] = chk.[LoginID]) AS [Checker]
,CAST(FORMAT(ET.[DateTime], 'yyyy-MM-dd hh:mm:ss', 'en-US') AS DATETIME) AS [Time] -- so that it doesn't round seconds to minutes (converting to SMALLDATETIME does that) and shows to the second.
,CASE
WHEN ET.[Question1] = 1 THEN 'Yes'
WHEN ET.[Question1] = 0 THEN 'No'
ELSE NULL
END AS [Question1]
,CASE
WHEN ET.[Question2] = 1 THEN 'Yes'
WHEN ET.[Question2] = 0 THEN 'No'
ELSE NULL
END AS [Question2]
FROM [dbo].[vw_Employees] AS HR
FULL JOIN -- to allow Visitors to be retrieved
(
SELECT
[Entry]
,[Temp]
,[CheckerID]
,[FullName]
,[EmployeeID]
,[DateTime]
,[Question1]
,[Question2]
FROM [dbo].[EmployeeTemperature]
WHERE CONVERT(DATE, [DateTime]) = CONVERT(DATE, GETDATE())
) AS ET
ON HR.[Employee_ID] = ET.[EmployeeID] -- encrypted
WHERE ET.[Entry] IS NOT NULL -- to not show unchecked employees.
GO
EXEC sp_refresh_parameter_encryption 'dbo.sp_GetEmployeeTemps';
当我尝试创建或更改上述过程时，我收到以下错误：
The data types char and char(10) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'Employee_Temperature') collation_name = 'Latin1_General_BIN2' are incompatible in the equal to operator.
这似乎表明问题出在这个 JOIN 子句上：
ON HR.[Employee_ID] = ET.[EmployeeID] -- encrypted
在此连接中，HR.[Employee_ID]未加密，并且是[vw_Employees]视图的一部分，并且ET.[EmployeeID]是加密列。
为什么这种平等加入不起作用？该文档指出加密列可以用于相等连接，这显然是。
这就是问题#1。
问题 #2 似乎与我ISNULL涉及加密列有关ET.[FullName]。
如果我注释掉该连接并ON 1 = 1出于调试目的进行，我会收到一个额外的错误：
Operand type clash: varchar(50) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'Employee_Temperature') collation_name = 'Latin1_General_BIN2' is incompatible with varchar
有什么建议来处理这种情况吗？
我已要求使用 GitHub 上的示例更新 MS Docs： https ://github.com/MicrosoftDocs/sql-docs/issues/4550
