I have created a table using:
create table dbo.employee(firstname nvarchar(100) null,lastname nvarchar(100) null)
Inserted some sample data using:
insert into dbo.employee values('Sachin','Tendulkar')
insert into dbo.employee values('Rohit','Sharma')
insert into dbo.employee values('Virendra','Sehwag')
insert into dbo.employee values('Irfan','Pathan')
Then I used always encrypted wizard to encrypt both columns of this table using SSMS v17. And now I am trying to concatenate firstname with lastname like this:
select concat(firstname, lastname) from dbo.employee
And it is giving me below error:
Operand type clash: nvarchar(100) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'SampleDB_CEK', column_encryption_key_database_name = 'SampleDB') is incompatible with varchar
When I try this:
select firstname + lastname from dbo.employee
It gives following error:
Encryption scheme mismatch for columns/variables 'firstname', 'lastname'. The encryption scheme for the columns/variables is (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'SampleDB_CEK', column_encryption_key_database_name = 'SampleDB') and the expression near line '1' expects it to be (encryption_type = 'PLAINTEXT') (or weaker).
Any help appreciated.
Concatenation in not allowed on encrypted columns. Currently the only operation possible on encrypted columns is equality. This is due to the fact that SQL Server does not have the key.
You might have to implement this logic in the client application.
From official documentation