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.

Its just been a few months of programming in SQL Server for me so my knowledge is not good in many regards. In an already existing project at work I came across many tables with large composite primary keys with clustered index. From what I have gathered a large column/composite column with clustered index hits performance very hard and at times the logical solution is an identity column. But at the same time I have come across many people flaming the overusage of identity column.

But along this finding I have never came across an example where identity column is a bad idea.

Recently we have standardized that every table should have an identity column as clustered index whether we use it as PK or not, as we require it for some export purposes.

So I would like some examples in real life scenarios where identity column as Clustered index is a bad idea.

Though at times it makes our life easy I have never encountered a scenario where it will be considered bad.

PS: I think my question is a bit naive but it is bugging me so much so I had to ask about it.

share|improve this question
    
Read Kimberly Tripp's blog posts on clustered index - she's the Queen of Indexing in the SQL Server world, and her basic recommendation is always to use INT IDENTITY as the primary (and clustered) key on almost any table. It's a well recommend best practice and usually works just fine. Cases where this is not a good idea are relatively rare in my opinion. –  marc_s Jun 20 at 15:23
    
Yes I have read those blogs and since following this practice. But I just wanted to know the cases where they will create problems, as mentioned below. :) –  Kai Jun 21 at 5:13

3 Answers 3

up vote 1 down vote accepted

I usually use an identity column as clustered primary key. However in some (rare?) cases this is not ideal because of the LastPageInsertLatchContention. This happens if a table is heavely filled with data. Because of the identity key all this INSERT's wants to write the last page of the table (index). So this page can be locked and the performance may be better with another solution.

See

for details.

share|improve this answer
    
I think "is heavely filled with data" should be more explicit: "has a consistently high volume of inserts" is the more direct cause of the system, not just that the table is large. –  Aaron Bertrand Jun 20 at 11:51
    
Of course you are right. This is only the case when a lot of rows will be inserted at the same time. It does not depend on the size of the table. –  Lothar Kraner Jun 20 at 13:02
    
So, I have a table in which we import lots of data (~ 100,000 rows in txt format) using bulk insert. I guess in this case identity clustered index might create a problem. –  Kai Jun 21 at 5:19
    
It depends :). First of all it depends on the load of the table the same time from other processes. The it also depends on the size of your rows (the more rows takes place in a single page of 8K, the more the problem can occur). Usually it is not a problem. I'm afraid you have to test it because changing this will usually be a big issue. Maybe other people faced the problem too. –  Lothar Kraner Jun 23 at 5:01

I never saw a identity column that is not also an index, usually the Primary Key.

Now we need to distinguish Primary Key (PK) and Clustered Index (CI) , the first is all about the logic of the database schema, the Primary Key is what make a row different from all the other in the table, and the Foreign Key for other tables. A identity column is always a Candidate Key, but it's artificial and you may want the natural Candidate Key as the PK.

Clustered Index instead is about how the index will be created from the data and stored. There can only be one clustered index and it will be the only index that refers to the data in the table. All the other indexes will refer to the clustered one.

Usually the PK is also the CI, but that's simply the default behaviour. I've seen, and sometime created, PK that were not CI: the PK was the Natural Key, the CI was the identity column. That because, simplifying the how index works, the smaller is the data in the CI definition, the faster the index is, and the CI need to be as fast as possible, so in case where the PK is huge having a identity column as the clustered index and make the PK a non clustered will improve the performances.

So in my opinion using a identity column as the clustered index is not a bad idea, but that doesn't mean that it should also be the primary key.

The only scenario I can think of where a identity column can be a bad choice is when there is a so high volume of incoming data that even the creation of the identity will hit the performance.

share|improve this answer

Which keys/indexes to cluster is not an exact science - the best use of a clustered index can vary depending on the table's use (and the use of the columns in that key).

The clustered key is more efficient for queries that pick out many rows in a range due to there being no need for extra row lookups to find the data for the rows found after searching the index. It helps for single row lookups too, but the difference is not as noticeable. For instance we have a tables that are often searched by object owner ID (rather than object ID which is the primary key), so it is more efficient for our app to have the index on that column be the clustered key, similarly it is sometimes much better to have the clustered key on commonly referenced date columns if rows over date ranges are often searched for.

If the PK of a given table is often a join target then clustering its PK can help as for certain join operations the reduction in further page lookups can be a big bonus, and of course if you have a PK based on real data (rather than a surrogate key like an auto-increment number or UUID) that is subject to ranged queries it has the benefits you'd expect. These reasons are why having your PK be clustered is generally a good position to start from before other considerations are taken into account, and hence why it is a common recommendation (and sometimes an automatically applied default).

As a side note: if you end up using a UUID column instead of an incrementing integer type as the PK on a table then clustering on it can be harmful to performance because the extra page splits created by inserting "random" data into the index (each page split on the clustered index results in extra IO activity on all the other indexes on the table too) which slows inserts and can exacerbate fragmentation issues over time. So in this situation it can often be much better to cluster a different index (or sometimes not have a clustered index at all, though this is not possible on SQL Server for Azure).

share|improve this answer

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.