Optimizing encrypted column search
- by Sung Meister
I have a table called,tblClient with an encrypted column called SSN.
Due to company policy, we encrypted SSN using a symmetric key (chosen over asymmetric key due to performance reasons) using a password.
Here is a partial LIKE search on SSN
declare @SSN varchar(11)
set @SSN = '111-22-%'
open symmetric key SSN_KEY decrypt by password = 'secret'
select Client_ID
from tblClient (nolock)
where convert(nvarchar(11), DECRYPTBYKEY(SSN)) like @SSN
close symmetric key SSN_KEY
Before encryption, searching thru 150,000 records took less than 1 second.
but with the mix of decryption, the same search takes around 5 seconds.
What strategy can I apply to try to optimize searching thru encrypted column?
