Index an encrypted column

[djj55]

Hello, SQL2008
If I have a column (SSN) that is encrypted, can or should I index it?

SSN is used to join other tables so do I need to decrypt the value to link the tables? My limited testing suggests that I do need to decrypt the column.

Now since it will be used for joins and criteria I would like to have an index. Is this possible or even practical?

Thank you,

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[SQLSister]

SSN should not be used in a join especially if it encrypted. That is a basic design flaw that you have. I would only store SSN in one table. Then have an SSN_Id (an identity field) if you need to put it into other tables and join on that.

Under no circumstances woudl I consider trying to join on an encypted field and SSN being a large varchar is not that good for performance for joining anyway.

"NOTHING is more important in a database than integrity." ESquared

 

[djj55]

Legacy. There are hundreds of things that are wrong with the data/database. Hey the main table has about 300 columns.

We are working toward revamp but trying to get rid of the Access front end first, however the encryption issue has come up.

If I had hair I would be pulling it out as SSN is in about twenty tables.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[SQLSister]

The encryption issue is more important than the Access front end. It should never have been stored without encryption to begin with. Even though you don't want to refactor and fix the structure, this is a case when you truly have no other viable choice. This is a showstopper problem.

BTW, SSN should never be a PK because it is not guaranteed to be unique, you realize this correct? Duplicates have been accidentally issued, people make typos, people steal othe people's SSN numbers and use them, etc. This is one of the worst PK choices you could have.

"NOTHING is more important in a database than integrity." ESquared

 

[djj55]

Our PK is a internal reference number. We are mindful of the security issues, but the SSN encryption has not been a problem before because we deal with estates. The encryption comes from the desire to increase our certification. Since we are currently using SQL 2000 and are looking to migrate to 2008 we thought this would be a good time to look into encryption.

There is one other column that needs encryption which also would be nice to index.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!