1

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!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
franko_camron
  • 1,288
  • 4
  • 14
  • 30
  • 2
    Bear in mind that being a primary key has nothing much to do with clustered indexes. The clustered index doesn't have to be a primary key and a primary key doesn't have to be a clustered index. – nvogel Jul 21 '11 at 09:49

1 Answers1

3

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)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • On the GUID front this looks like it might turn [into an interesting series](http://sqlblogcasts.com/blogs/tonyrogerson/archive/2011/07/18/use-guid-s-uniqueidentifier-for-keys-when-storing-data-on-ssd-s-part-1-ssd-backgrounder.aspx) – Martin Smith Jul 21 '11 at 00:22
  • @Nartin: Kim Tripp has already done some investigation into GUID and SSD, too : http://www.sqlskills.com/BLOGS/KIMBERLY/post/Disk-space-is-cheap.aspx – marc_s Jul 21 '11 at 04:57