SQL Server 2012 Transparent Data Encryption and indexes

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.

StackExchange Database Administrators Q#76739, answer score: 4