When is the Identity Column as Clustered Index a Bad Idea?

Question

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 over-usage of identity columns.

But 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 the 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 using an identity column as a 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.

Answer 1

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

• http://dangerousdba.blogspot.ch/2011/10/bit-reversion.html

• Link

• http://www.sqlpassion.at/archive/2014/04/15/an-ever-increasing-clustered-key-value-doesnt-scale/

for details.

Answer 2

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.

Answer 3

I would like some examples, in real life scenarios, where using an identity column as a clustered index is a bad idea.

Generally it's a bad idea whenever the identity Clustered Index would simply be a redundant, extra index. You only get one clustered index, so if you pick the wrong one, it's going to add cost to all of your transactions.

Whenever you already need a compound key, or a natural key, having an identity column as a clustered index is a bad idea.

Two common scenarios that should use compound keys are "linking tables", and "nested tables" eg:

create table a(id int identity primary key)
create table b(id int identity primary key)
create table a_b
( 
  a_id int not null references a,
  b_id int not null references b,
  constraint pk_a_b primary key (a_id,b_id),
  constraint ak_a_b unique (b_id, a_id)
)

Adding an identity column clustered index is useless and harmful.

A common example of the second is "nested" tables, where a single compound PK is the only index necessary:

create table a(id int identity primary key)
create table a_detail
(
  a_id int not null references a,
  id int not null identity, 
  constraint pk_a_detail primary key (a_id,id) 
)

Uncontroversial use cases for natural keys include lookup tables eg

create table region
(
  region_code char(3) not null primary key,
  name nvarchar(200),
  description nvarchar(200)
)

Slightly more controversial, but IMO correct, is the use of sequential UNIQUEIDENTIFIER as a clustered PK, and so that's also a scenario where adding an IDENTITY column with a clustered index is harmful.

Answer 4

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^[1] and it is rare that not having a clustered key is a benefit rather than a detriment overall).

^{[1] it has been possible to have a heap (a table without a clustering key) on Azure SQL for some time now, though with similar caveats as found in on-pre SQL Server of it rarely being a great idea}

Answer 5

If you're implementing detail tables and want to retain a single-column primary key, here's an approach worth considering:

CREATE TABLE Parent (
    Parent_ID int NOT NULL IDENTITY(1,1),
    Parent_Data varchar(100) NULL,
    CONSTRAINT PK_Parent PRIMARY KEY CLUSTERED (Parent_ID)
);

CREATE TABLE Parent_Detail (
    Parent_Detail_ID int NOT NULL IDENTITY(1,1),
    Parent_ID int NOT NULL,
    Detail_Data varchar(100) NULL,
    CONSTRAINT PK_Parent_Detail PRIMARY KEY NONCLUSTERED (Parent_Detail_ID),
    INDEX CX_Parent_Detail UNIQUE CLUSTERED (Parent_ID, Parent_Detail_ID),
    CONSTRAINT FK_Parent_Detail_Parent FOREIGN KEY (Parent_ID) REFERENCES Parent (Parent_ID)
);

I set up the Parent table with a clustered primary key on the identity column. For the Parent_Detail table, the identity column is the primary key, but the clustering index is on the foreign key (Parent_ID) followed by the identity column. By adding the identity column into the clustering index (as David Browne did in his solution) and then defining the clustering index as unique, we avoid the 4-byte uniquifier. Even though the uniquifier is only added when necessary for specific records (https://sqlquantumleap.com/2017/09/18/clustered-index-uniquifier-existence-and-size/ has a good writeup on this), I feel better defining my non-primary key clustered indexes with UNIQUE when possible.

By clustering with Parent_ID column in the leading position, we enable clustered index range-scan for identifying detail records for a given parent record, which should improve performance for this common use case.

Answer 6

I was looking for an advice on the opposite question: "Should I always use identity PK"? And the answer is, obviously: "no".

My situation: Big table with history of one value.

CREATE TABLE HistoryOfValue (
  Item_ID INTEGER,
  ItemDistinguisher_ID INTEGER,
  PointInTime DATETIME2,
  Value DECIMAL,
  PRIMARY KEY (Item_ID, ItemDistinguisher, PointInTime)
)

The table will be poupulated semi-randomly. Just the "PointInTime" will only grow. We will then perform lots of selects filtered by Item_ID and ItemDistinguiesher_ID and an interval of PointInTime.

I know this table will be badly fragmented, but if I created the same table with an identity clustered key, I would need to also add an index, which would be as badly fragmented as the table without the identity PK. The selects would than either need to perform lots of index lookups or the other index would need to include the Value column. And this index would be as heavily fragmented as the table without the identity would.

(please do not comment that we should use a data warehouse or something similar, we are currently fine with this solution)