KB #240032: Implications of Column Encryption on Queries When Using Views

Type:	Information

Summary:
For views where a column that is encrypted on disk is presented as decrypted in a view, there are issues that needs to be understood regarding performance.

Additional Information:
This topic discusses the impact of encryption on “transparent views”, i.e., views that present an encrypted column as decrypted. Consider the following query on the transparent view “Employees”, where the SSNO column is transparently encrypted, i.e., the view name replaces the underlying table name, the SSNO column is encrypted on disk, but presented decrypted in the view: select * from Employees where SSNO = ‘123456789’ With a transparent view, this effectively becomes the following query: select * from Employees where fn_n_decrypt_char(SSNO) = ‘123456789’ Even though it is a unique value, SQL must still decrypt every row to make the comparison. Again, with a small table of a few thousand records, this may not make a large difference. For a large table, you will get far better performance using non-transparent views using the query above, where you are looking up the unique encrypted value, for example: select * from employees where ssno = fn_n_encrypt_char(@cSSno) Be aware that queries may take longer when data is encrypted.

Related Topics:
240031	Considerations When Implementing Column Encryption and the Impact on Performance
240005	Cannot Perform Case-Insensitive Compare on Encrypted Strings Using APIs

Last modified: 1/13/2016