Does SQL Server TDE allow a query like this:

     select * from Patient where lastname = 'Smith'

to occur without a full-table scan if the lastname column is indexed?


Yes. Transparent Data Encryption doesn't change any of the internal processing of queries. Database pages are transparently encrypted/decrypted during I/O (i.e. when they are read from or written to disk). In-memory query processing is therefore unaffected, so indexes continue to function exactly as they did without TDE.

TDE is designed to negate the need for application developers to make any changes to their applications. http://msdn.microsoft.com/en-us/library/bb934049.aspx contains a salient piece of information:

This enables software developers to encrypt data by using AES and 3DES encryption algorithms without changing existing applications.

In short, queries operate no differently with TDE enabled or disabled, aside from the performance impact of the encryption and decryption activities.

| improve this answer | |
  • I understood that "transparent" meant no coding changes, but it wasn't clear about what went on "under the covers". Were simple column or multi-column indexes taken advantage of? On the page you provided, full-text indexes are discouraged: Enabling a full-text index on a column can cause that column's data to be written in plain text onto the disk during a full-text indexing scan. We recommend that you do not create a full-text index on sensitive encrypted data. – Tim Sep 16 '14 at 17:05
  • That warning against full-text-indexes is only important since unencrypted data may be written to disk; which is a possible security issue. – Max Vernon Sep 16 '14 at 17:31
  • So TDE caches unencrypted versions of simple indexes in RAM, to enable it to quickly find the relevant rowids in the primary table? Or does it scan the entire index and decrypt it afresh with each new query? – Tim Sep 16 '14 at 17:46
  • TDE reads encrypted pages from disk, decrypts them into RAM, whereupon they are not touched by the TDE code until they need to be written to disk. At that point, the TDE code encrypts the pages (or log entries, as may be the case) just prior to writing them to disk. – Max Vernon Sep 16 '14 at 17:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.