How to perform Join operation with Encrypted and Non Encrypted Column in Sql server 2016. Im using .net framework 4.6.1. Column Encryption Setting = Enabled in Connection string.
Code :
cmd = new SqlCommand("select determin.name as name from determin inner join determinjoin on determin.name = determinjoin.name ", con);
Error :
The data types varchar(20) encrypted with (encryption_type =
'DETERMINISTIC', encryption_algorithm_name =
'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto4',
column_encryption_key_database_name = 'depdb') collation_name =
'Latin1_General_BIN2' and varchar are incompatible in the equal to operator.
As is, you cannot. The query processor does not have access to the key to decrypt for the join. If you REALLY must join the two tables, you should encrypt both columns with deterministic encryption.
That said, you might still not want to do it. Performance will be anywhere from bad to abysmal for anything more than a few million rows. If you really must and there's no option to modify the schema, then test and see if your users still feel the same after they see how it performs. If you can make small tweaks to the schema, there are options to make this easier. For instance, using some alternate column for such joins. Could be a surrogate key or perhaps even a natural key that isn't security sensitive.