I'm new in SQL Server development and I'm currently reading a book, the thing is that in this book I read that if I update an indexed column I will affect all the records in that column because an index is like the index of a book.
In my work, the SSN (social security Number) is the primary key table, and I've been asked to enable de SSN modification on our application.
So I am wondering.... what is the cost of modifying a clustered index column?
Thanks in advance!
The cost is quite high - another reason why a good clustering key should be static, e.g. never change.
The clustered index column(s) are actually present inside each and every non-clustered index, too - so if you have a table with 10 non-clustered indices, updating the value of a column used in the clustering key on that table will need to go out and update all 10 non-clustered indices, too.
Read Kimberly Tripp's blog post Ever-increasing clustering key - the Clustered Index Debate..........again! to learn what constitutes a good clustering key on a table.
To sum up briefly - a good clustering key should be:
narrow - 4 bytes is perfect, 8 bytes is tolerable, anything beyond that is getting bad on performance....
unique - the clustering key is the ultimate lookup for your data - if that column (or set of columns) isn't unique, SQL Server will add a 4-byte uniquefier to your data - not recommended....
static - the value of the clustering column(s) should never change - for performance reasons
ever-increasing - to avoid index fragmentation
INT IDENTITY or BIGINT IDENTITY are thus the best options for you - if you can, use one of those. Avoid large compound indices, avoid large variable-length string columns, avoid GUID (heavy on fragmentation and thus bad for performance)