Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Indexing on Encrypted Columns

Author  Topic 

lionofdezert
Aged Yak Warrior

885 Posts
Posted - 2008-11-20 : 07:44:57
as most of columns in my database contains encrypted data, but i want to apply indexes on these encrypted columns, to avoid performance issues. How to apply indexes on such encrypted columns ?

Note: I don't want to use indexed views.


thanks in advance

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2008-11-20 : 08:50:07
what performance are you trying to improve?
indexes increase search speed. for encrypted data you still have to decrypt everthing and searhc through that.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!
Go to Top of Page

lionofdezert
Aged Yak Warrior

885 Posts
Posted - 2008-11-20 : 10:39:16
yes i used views for search purpose where data is in decrypted form. But is it possible to apply indexes on these encrypted columns in table along with other columns which are not encrypted.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2008-11-20 : 10:43:09
well you can put an index on encrypted column but there's no point. so basicaly the answer is no.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!
Go to Top of Page

lionofdezert
Aged Yak Warrior

885 Posts
Posted - 2008-11-20 : 11:04:42
Thanx spirit for your quick response
Is it a good practice to create a simple select view from my original table and apply indexes on this view instead of table.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2008-11-20 : 11:16:28
that should suffice. but then what's the point of encrypting the data in the first place?

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!
Go to Top of Page

lionofdezert
Aged Yak Warrior

885 Posts
Posted - 2008-11-20 : 11:33:56
Encryption of few columns is requirement of client, they want to secure their data.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2008-11-20 : 11:47:56
ok i understand that... but if you have access to decrypted data via view the encryption itself isn't any use, no?
just trying to understand the reasoning.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!
Go to Top of Page

lionofdezert
Aged Yak Warrior

885 Posts
Posted - 2008-11-20 : 11:57:40
We can encrypt definition of our views, procedures and functions but not TABLES.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2008-11-20 : 12:14:39
aha, cool.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!
Go to Top of Page

lionofdezert
Aged Yak Warrior

885 Posts
Posted - 2008-11-20 : 12:22:39
Thanx Spirit, when ever i was in trouble you experts were always here, to help.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site