Otdfbt

Example use case: We want to protect the privacy of potential patients, that is, keep their medical procedures, if any, from being divulged. Here is a simplistic schema to illuminate the issues that I have questions about relating to Microsoft SQL Server encryption options. The questions are at the bottom.

Table1: POTENTIALPATIENTS
This is a set of people who are authorized to use the medical services. About 250,000 rows. NOTE: We need to do (rapid response) full-text searching on patientname. We also want to prevent duplicates as best we can in the absence of something like a "unique patient number".

ppid uniqueidentifier primary key
patientname nvarchar(100)
birthdate date
last4digitsOfSSN char(4)

-- prevent duplicates as best we can
CREATE UNIQUE INDEX UX_POTENTIALPATIENTS on 
POTENTIALPATIENTS(patientname, birthdate, last4digitsOfSSN)

-- for lookups using `like` or `contains` operators
(pseudo) CREATE FULLTEXT INDEX ON PATIENTNAME

Table2: MEDICALPROCEDURES
This is the table showing the medical procedure(s) a potential (now actual) patient has had.

id identity(1,1) primary key
ppid uniqueidentifier 
procedurecode varchar(10)
proceduredate date

ALTER TABLE MEDICALPROCEDURES ADD CONSTRAINT
FK_MEDICALPROCEDURES_POTENTIALPATIENTS
FOREIGN KEY(ppid) REFERENCES POTENTIALPATIENTS(ppid)

-- to quickly find all procedures for a given patient
CREATE INDEX IX_MEDICALPROCEDURES_PPID on MEDICALPROCEDURES(ppid)

Forgetting for a moment about our other application requirements (duplicate prevention, fast name lookups, fast gathering of a specified patient's procedures) we can protect patient privacy (that is, keep hidden what procedures they have had, if any) by doing either of these things:

a) encrypt column patientname, which would obscure the patient name

or

b) encrypt columns POTENTIALPATIENT.ppid and MEDICALPROCEDURES.ppid, the columns involved in the foreign-key constraint, which would obscure which patients have had which procedures

QUESTIONS:

If we encrypt patientname can we do quick patient-name lookups using LIKE/CONTAINS operators? Can patientname participate in a unique composite index?

If we encrypt the uniqueidentifier columns in the foreign-key relationship, can we quickly find all procedures a specified patient has had, avoiding a full table scan?