Always Encrypted: How do I do an equality join with a non-encrypted column to a deterministic encrypted column?

The official Microsoft documentation for Always Encrypted on SQL Server 2017 states: > Deterministic encryption always generates the same encrypted value for any given plain text value. > > Using deterministic encryption allows point lookups, **equality joins**, grouping and indexing on encrypted columns. (bold emphasis mine) I'm currently using SQL Server 2017 RTM-CU17 (KB4515579) v14.0.3238.1 Standard Edition. My SSMS (currently using v18.4) connection is already configured with the Enable Always Encrypted (column encryption) checkbox checked, and the Query Options -> Execution -> Advanced setting Enable Parameterization for Always Encrypted is also checked. Below is the table schema I have. The EmployeeID and FullName columns are encrypted with Deterministic Encryption Type. The Temp column is encrypted with 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 The below Stored Procedure code is used to retrieve the encrypted data (eventually will be used by an ASPX Single Page Application (SPA) web app). --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'; When I try to create or alter the above procedure, I receive the below error:

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.

This seems to indicate that the issue is with this JOIN clause: ON HR.[Employee_ID] = ET.[EmployeeID] -- encrypted In this join, the HR.[Employee_ID] is not encrypted, and part of the [vw_Employees] view, and the ET.[EmployeeID] is the encrypted column. Why does this equality join not work? The documentation states that encrypted columns can be used in equality joins, which this clearly is. That's problem #1. ---------- Problem #2 seems to be with my ISNULL involving the encrypted column ET.[FullName]. If i comment out that join and do ON 1 = 1 for debugging purposes, I get an additional error:

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

Any suggestions to handle that scenario? I have requested the MS Docs be updated with examples on GitHub: https://github.com/MicrosoftDocs/sql-docs/issues/4550