Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Our databases consist of lots of tables, most of them using an integer surrogate key as a primary key. About half of these primary keys are on identity columns.

The database development started in the days of SQL Server 6.0.

One of the rules followed from the beginning was, Avoid creating a clustered index based on an incrementing key, as you find in these Index Optimization Tips.

Now using SQL Server 2005 and SQL Server 2008, I have the strong impression that the circumstances changed. Meanwhile, these primary key columns are perfect first candidates for the clustered index of the table.

share|improve this question

4 Answers 4

up vote 28 down vote accepted

The myth goes back to before SQL Server 6.5, which added row level locking. And hinted at here by Kalen Delaney.

It was to do with "hot spots" of data page usage and the fact that a whole 2k page (SQL Server 7 and higher use 8k pages) was locked, rather then an inserted row Edit, Feb 2012

Found authoritative article by Kimberly L. Tripp

"The Clustered Index Debate Continues..."

Hotspots were something that we greatly tried to avoid PRIOR to SQL Server 7.0 because of page level locking (and this is where the term hot spot became a negative term). In fact, it doesn't have to be a negative term. However, since the storage engine was rearchitected/redesigned (in SQL Server 7.0) and now includes true row level locking, this motivation (to avoid hotspots) is no longer there.

Edit, May 2013

The link in lucky7_2000's answer seems to say that hotspots can exist and they cause issues. However, the article uses a non-unique clustered index on TranTime. This requires a uniquifier to be added. Which means the index in not strictly monotonically increasing (and too wide). The link in that answer does not contradict this answer or my links

On a personal level, I have woked on databases where I inserted tens of thousands of rows per second into a table that has a bigint IDENTITY column as the clustered PK.

share|improve this answer

Kimberly Tripp has a fantastic blog post about just this topic. I could paraphrase, but trust me, I wouldn't do it justice. Have a read. http://www.sqlskills.com/BLOGS/KIMBERLY/post/Ever-increasing-clustering-key-the-Clustered-Index-Debateagain!.aspx

While there, check out some of her other posts on the topic of clustering keys. There is a good wealth of knowledge to be had from her site.

share|improve this answer

To sum it up, in modern SQL Server versions a clustered key on an identity column is the preferred option these days.

share|improve this answer
    
Short, simple, to the point so this gets my +1. Do be sure to check out the link to SQLSkills as there is a wealth of good info there. –  AndrewSQL Mar 6 '11 at 14:17
7  
This sounds like a command. No explanation or logic as to why we should... –  gbn Feb 1 '12 at 8:37

check this post:

http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/monotonically-increasing-clustered-index-keys-can-cause-latch-contention.aspx

creating a clustered index based on an incrementing key may create hot spots that bad for the performance...

share|improve this answer
    
+1 for giving that link. There are some interesting hints there. But I think the result would be much more convincing, if he had compared the the given scenario with one with create nonclustered index cidx_trantime on tblTransactions (TranTime) or some other alternative. Remember when you generate such a lot of data there must be efficient ways to retrieve the data, you can't just throw every thing into a heap. –  bernd_k Sep 18 '11 at 13:52
    
@bernd_k: this is a poor example link. The child table has a bad non-unique clustered key that requires an internal uniquifier –  gbn May 2 '13 at 14:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.