Searching an Encrypted Column

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