Overview
Always Encrypted protects sensitive data such that the database engine never sees plaintext values. Two encryption types are available for column encryption:
- Deterministic – produces the same ciphertext for identical plaintext values.
- Randomized – generates a different ciphertext each time a value is encrypted.
When to Use Deterministic Encryption
Deterministic encryption enables equality searches, grouping, and joining on encrypted columns because identical plaintext values map to identical ciphertext.
-- Example: Find rows with a specific SSN
SELECT *
FROM dbo.Customers
WHERE EncryptedSSN = EncryptByKey(Key_GUID('CEK_Deterministic'), N'123‑45‑6789');When to Use Randomized Encryption
Randomized encryption provides stronger security guarantees by adding randomness (IV) to each encryption operation, making ciphertext analysis infeasible.
-- Example: Insert a new row with randomized encryption
INSERT INTO dbo.Employees (EmployeeId, EncryptedSalary)
VALUES (101,
EncryptByKey(Key_GUID('CEK_Randomized'), CONVERT(varbinary, 85000.00))
);Comparison Table
| Feature | Deterministic | Randomized |
|---|---|---|
| Searchability | Equality queries supported | Only exact match after decryption |
| Indexing | Can create non‑clustered indexes | Indexes not allowed |
| Security | Vulnerable to frequency analysis | Higher confidentiality |
| Performance | Faster reads for searchable columns | Higher CPU overhead on writes |
| Use Cases | Lookup tables, foreign keys, deduplication | Highly sensitive data such as credit card numbers |
Best Practices
- Use deterministic encryption only for columns that require search or join operations.
- Prefer randomized encryption for columns that store highly confidential data and do not need to be searchable.
- Rotate column encryption keys regularly using
ALTER COLUMN ENCRYPTION KEY. - Leverage client‑side drivers that support Always Encrypted (e.g., ADO.NET, JDBC).