January 11, 2011 at 9:28 am
Hi,
Is it a good idea to create a clustered index on Varchar datatype column which holds SSN.
Thanks,
Narendra
January 11, 2011 at 10:29 am
There are 2 tables with millions of records. On table 1 a clustered index is created on varchar datatype column which holds SSN. There are no duplicate values
On table 2 also a clustered index is created on varchar datatype column which holds SSN. However there are duplicate values in this column.
My doubt is - - Is it a good practice to create a clustered index on varchar datatype which holds SSN.
Please suggest
January 11, 2011 at 10:51 am
In general I don't like to do it if I have a choice.. Also, I am opposed to using a column containing personally identifiable information (PII) as a key for joins and I would want to limit it to only ONE place in the database. Depending on your industry this might even be illegal or at LEAST contract breaching..
The very first thing I thought of was this article from the other day:
Natural vs. Surrogate Keys in SQL Server : Getting the Proper Perspective[/url]
I would look there for additional guidance.
CEWII
January 11, 2011 at 11:02 am
I would not pick a varchar as my first choice for a clustered index... or my second. SSN's are not like GUID's because there is a structure to SSN. It's not random like a GUID. So if the structure you were dealing with lead you to the point that the most selective AND most frequently accessed column is the SSN, you could make it the cluster. Just remember that all other indexes on the table will be bigger because they're storing the cluster key within them.
As to making it the primary key... heck no. That data is subject to change (bad entries, lost or replaced SSN's, etc.). That makes it a very bad choice as a primary key.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply