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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.