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.
| Author |
Topic |
|
lionofdezert
Aged Yak Warrior
885 Posts |
 Posted - 2008-11-21 : 02:21:56
|
| While creating clustered index on a view, i received following errorThe function 'DecryptByKeyAutoCert' yields nondeterministic results. Use a deterministic system function, or modify the user-defined function to return deterministic results. (.Net SqlClient Data Provider)Reason:My actual table on which this view depends have few colomns with encrypted data, I created this view to get decrypted data but need to apply some indexes on these decrypted data columns but unable to achive my goal due to above mentioned error.What should i do ? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-21 : 02:24:34
|
| view can be indexed only if it satisfies lot of condition see thishttp://www.sqlteam.com/article/indexed-views-in-sql-server-2000 |
 |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2008-11-21 : 02:50:32
|
| Thanx visakh for your reply but link said:"Views cannot use non-deterministic functions."But i have no other option to place these decryption functions (which are non-deterministic)i need to apply indexes on encrypted columns.If it is possible in table then i will apply these indexes on table level.Any other option where i can apply these indexes. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-21 : 03:10:10
|
| Try creating another table with decrypted info from first and put index on it. |
|
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2008-11-21 : 05:53:05
|
| sorry visakhbut it does not seem a perfect approach. |
|
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2008-11-22 : 10:56:45
|
| any body like to help plz ? |
|
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2008-11-22 : 16:45:50
|
| I'm curious why your decryption function isn't deterministic. Any chance you can post the code? Also, if indexes on the decrypted data are important, what is the importance of storing encrypted values? It's hard to offer advice without knowing more about your situation, but the face value of visakh16's idea is reasonable: if an indexed view isn't going to solve your problem, use a solution that will. Try using triggers to maintain the 'decrypted table' and you'll have the same effect as an indexed view.If you had control over how the data gets into your system, you could potentially fork out the decrypted data from that process. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-11-22 : 17:37:47
|
| Forget creating an index based on the decrypted value.Add an additional column to the table with a hashed value of the plaintext of your encrypted column and put the index on that column. Then if you need to find a row containing a particular value in the encrypted column, hash the plaintext and do a lookup on the hashed column.CODO ERGO SUM |
|
|
|
|
|
|
Unable to create index on a view