May 27, 2011 at 6:24 pm
The best way I can think of to ask the question is to write out the code...
Example table
tblTest
TID
col1
col2
encryptedCol1
From what I've read, having an encrypted column as a key won't do any good. You'd need to have a hash column for it to be searchable. However, my question is will this work:
CREATE NONCLUSTERED INDEX index1 ON tblTest(col1, col2) INCLUDE(encryptedCol1)
Using a select statement like: SELECT encryptedCol1 FROM tblTest WHERE col1 = 'value'
Specifically, will SQL use the index to retrieve the encrypted column and just decrypted it or will it need to hit the table? Basically making that included encrypted column useless.
Thank you in advance.
May 27, 2011 at 7:17 pm
I do not know of the top of my head but it's an interesting question so thanks for asking it here 🙂
Do you have a test system where you can create the index to try it and see what it does?
If you run the query and the execution plan (Estimated plan may be enough to see) says it's only using the index to deliver the result then great...but if it's doing a lookup to get the value from the table (i.e. clusterd index) so it can decrypt it then there is probably no sense in including the encrypted column the index.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 28, 2011 at 3:20 am
It makes absolutely no difference here whether the include column is encrypted or not. What you have there is a straightforward select with a SARGable predicate against a covering index.
What you have there is absolultely no different from this scenario:
CREATE INDEX idx_Test ON Test (col1) Include (SomeStringColumn)
SELECT UPPER(SomeStringColumn) FROM Test WHERE Col1 = @SomeVar
Where encryption and indexes don't get along is when you are searching the encrypted column. Because decrypting uses a function on the column (decryptbxxx(<column name>)), if that is used as a predicate, the predicate is not SARGable, so index scan time, and you can't encrypt and compare the encrypted values as the encryption routines are not deterministic.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2011 at 9:51 am
I was originally thinking you were asking about TDE, but now I suspect you're asking about column-level encryption, correct?
If so then Gail is spot-on (as usual).
PS If you are using TDE then you are not forced to call a function to decrypt data, it's done at the page level (as I have since learned), so indexes work the exact same way as if TDE was not in place.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply