Monday, March 19, 2012

Indexing on Decryption Views

1) We have SQL Server 2005 on windows 2003 server.
2) In some columns of some tables, data is encrypted before being stored and indexes
have no meaning against these columns.
3) We have decryption views to select all columns of each & every table while decrypting
the encrypted ones.
4) All our code i.e. only queries is in stored procs. We use only decryption views in these
queries.
5) When encryted columns are involved in join or filter, query runs very slow and time outs
are occuring.
6) Indexes on decrypted columns will not work. Can indexes on decryption views against these
columns help? If so, how?
7) If so, please give an example creation of such an index on a view and its column OR post
a good link?

There are indexed views but, even if you could create an index on a view that decrypted the data, the decrypted data would be materialized and there would be no point in encrypting it in the first place.

You can create a variety of temporary tables into which you insert decrypted data which you could then index but SLOW gererally applies.

Query timeouts can be adjusted.

No comments:

Post a Comment