When encrypting a database column, equality searching can be preserved by using a deterministic encryption algorithm. A deterministic algorithm will always return the same encrypted value for a given value. For example, if a [Customer] table is defined as follows: (Note: I’ve left out usual constraint syntax for brevity)
CREATE TABLE dbo.Customer ( ID INT, Name NVARCHAR(100), BirthDate DATE, SSNEncrypted VARBINARY(8000) )
When searching for a specific SSN, simply encrypt the parameter and issue a query against the table:
SELECT Name FROM dbo.Customer WHERE SSNEncrypted = @EncryptedSSNValue
This method works well for both equality as well as non-equality