Should the index on an identity column be nonclustered?

Question

For a table with identity column, should a clustered or non-clustered PK/unique index be created for the identity column?

The reason is other indexes will be created for queries. A query which uses a nonclustered index (on a heap) and returns columns that are not covered by the index will use less logical I/O (LIO) because there are no extra clustered index b-tree seek steps?

create table T (
  Id int identity(1,1) primary key, -- clustered or non-clustered? (surrogate key, may be used to join another table)
  A .... -- A, B, C have mixed data type of int, date, varchar, float, money, ....
  B ....
  C ....
  ....)

create index ix_A on T (A)
create index ix_..... -- Many indexes can be created for queries

-- Common query is query on A, B, C, ....
select A, B 
from T 
where A between @a and @a+5 -- This query will have less LIO if the PK is non-clustered (seek)

select A, B, C
from T 
where B between @a and @a+5 

....

Clustered PK on identity column is good because:

1. It increase monotonously so no page splits when inserting. It's said a bulk insert can be as fast as on a heap (nonclustered) table

2. It's narrow

However, will the queries in the question be faster without setting it clustered?

** Update:** What if the Id is the FK of other tables and it will be joined in some queries?

Answer 1

By default the PK is clustered and in most cases, this is fine. However, which question should be asked:

• should my PK be clustered?
• which column(s) will be the best key for my clustered index?

PK and Clustered index are 2 differences things:

• PK is a constraint. PK is used to uniquely identify rows, but there is no notion of storage. However by default (in SSMS), it is enforced by a unique clustered index if a clustered index is not yet present.
• Clustered indexes is a special type of index which store row data at the leaf level, meaning it is always covering. All columns whether they are part of the key or not, are stored at the leaf level. It does not have to be unique, in which case a uniquifier (4 bytes) is added to the clustered key.

Now we end up with 2 questions:

• How do I want to uniquely identify rows in my table (PK)
• How do I want to store it at the leaf level of an index (Clustered Index)

It depends on how:

• you design your data model
• you query your data and you write your queries
• you insert or update your data
• ...

First, do you need a clustered index? If you bulk insert, it is more efficient to store unordered data to a HEAP (versus ordered data in a cluster). It uses RID (Row Identifier, 8 bytes) to uniquely identify rows and store it on pages.

The clustered index should not be a random value. The data at the leaf level will be stored and ordered by the index key. Therefore it should grow continuously in order to avoid fragmentation or page split. If this can not be achieved by the PK, you should consider another key as a clustered candidate. Clustered index on identy columns, sequential GUID or even something like the insertion's date is fine from a sequential point of view since all rows will be added to the last leaf page. On the other hand, while unique identifier may be useful to your business needs as a PK, they should not be clustered (they are randomly ordered/generated).

If after some data and query analysis, you find out that you mostly use the same index to get your data before doing a key lookup in the clustered PK, you may consider it as clustered index although it may not uniquely identify your data.

The clustered index key is composed of all the columns you want to index. A uniquefier column (4 bytes) is added if there is no unique constraint on it (incremental value for duplicates, null otherwise). This index key will then be stored once for each row at the leaf level of all your nonclustered indexes. Some of them will also be stored several times at intermediate levels (branch) between the root and the leaf level of the index tree (B-tree). If the key is too large, all the non clustered index will get larger, will require more storage and more IO, CPU, memory, ... If you have a PK on name+birthdate+country, it is very likely that this key is not a good candidate. It is too large for a clustered index. Uniqueidentifier using NEWSEQUENTIALID() is usually not considered as a narrow key (16 bytes) although it is sequential.

Then once you figured out how to uniquely identify rows in your table, you can add a PK. If you think you won't use it in your query, don't create it clustered. You can still create another nonclustered index if you sometime need to query it. Note that the PK will automaticaly create a unique index.

The non clustered indexes will always contain the clustered key. However, if the indexed columns (+key columns) are covering, there won't be any key lookup in the clustered index. Don't forget you can also add Include and Where to a non clustered index. (use it wisely)

Clustered index should be unique and as narrow as possible Clustered index should not change over time and should inserted incrementally.

It is now time to write some SQL which will create the table, clustered and nonclustered indexes and constraints.

This is all theoritical because we don't know your data model and datatypes used (A and B).

Answer 2

For a table with a primary key (PK) on an identity column, it will be clustered by default. Could it be better as nonclustered?

If you're asking if the default for a primary key on an identity column (in particular) ought to be nonclustered, I would say no. Most tables benefit from having a clustered index, so making clustered the default for a primary key constraint is probably helpful overall, especially for new users of SQL Server.

As with pretty much any option, there are always different circumstances where one is to be preferred over the other, but an experienced DBA should be aware of the default, and be able to override it when appropriate. Also see the related Q & A, When should a primary key be declared nonclustered?.

Will the queries in the question be faster without setting it clustered?

Yes, but with caveats.

RID lookups are indeed more efficient than Key lookups. Even if all required pages are in memory (very likely for the upper levels of an index), there is a CPU cost associated with navigating the clustered index b-tree. As a consequence, SQL Server can typically perform many more RID lookups than Key lookups per unit of CPU time.

Caveats

The above would not often be a determining factor when deciding whether to structure a table as a heap or not. It would have to be impractical to avoid lookups (using covering indexes), and the number of lookups would have to be large enough to have a measurable (and important) effect on performance, given the hardware environment and workload.

It is not really practical to cover all aspects of the heap vs clustered index debate in this answer, but I will say that there are relatively few good reasons to prefer to structure a table as a heap in general. For me, choosing the sort of design proposed in the question would require a very careful analysis before implementation, and would have to meet a high bar. General arguments about 'scalability' would not be sufficient.

Regarding the update to the question about joins, assessing the impact of losing the clustered index on execution plans would form part of the analysis mentioned above. If nested loops joins are used, it is very convenient to have the clustered index on the join key because all columns from the row are immediately available without a lookup.

My own experience has been that having unique clustered indexes on identity columns is very often beneficial, all things are considered. I have found heaps problematic in terms of space management, and I should also mention that some SQL Server features require a unique clustered index to function.

Answer 3

Actually, you do not need a Clustered Index nor a Primary Key to be created, since Unique Indexes and Non-Unique Indexes can handle the work. SQL Server has supported a Clustered Index since at least version 1.1, but the Primary Key was just a "concept" that programmers enforced by defining a unique index.

But it seems that both Primary Keys and Clustered Indexes are valuable concepts in the majority of databases.

Let us look at the SQL Server documentation to see the partial descriptions of some indexing options as show below.

Clustered Index: https://msdn.microsoft.com/en-us/library/ms190457.aspx

• Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition.
• There can be only one clustered index per table

Primary Key: https://msdn.microsoft.com/en-us/library/ms190457.aspx

• A table can contain only one PRIMARY KEY constraint.

• All columns defined within a PRIMARY KEY constraint must be defined as NOT NULL.

• The Primary Key can be created as a Clustered Index (the default if there is no Clustered Index) or a Non-Clustered Index.

Unique Index: https://msdn.microsoft.com/en-us/library/ms187019.aspx

• When you create a UNIQUE constraint, a unique nonclustered index is created to enforce a UNIQUE constraint by default.

• You can specify a UNIQUE Clustered Index if a Clustered Index does not already exist for the table.

This means that your question about Clustered Indexes and Primary Keys is really about some of the following issues. Please note that not every table benefits from the same indexing plan.

When would I benefit from the Primary Key being separate from the Clustered Index?

Perhaps when the Clustered Index is Wide (for example, 5 columns of textual information, but the Primary Key is small (INT or BIGINT), such as you seem to be describing.

• A wide Clustered Index would allow you to quickly select rows from the index for a subset of queries that provide serial answers from the Clustered Index (also known as the Table). For example, a 5-column Clustered Index would support scanning the columns C1, C2, C3, C4, C5 or C1, C2, C3, C4 and so on down to C1.
• Note: If the rows were large, this might give you some speed benefits on selecting the serial set of rows, especially if other columns in the table are regularly included in the result set.
• In that case you can use the Primary Key for referential integrity in order to supply the needed value as a Foreign Key to constrain rows in other tables. The PK is small and is thus the FK is a small hit on the size of the referenced table(s).
• However, note that any index created on a table that has a Clustered Index will include all of the cluster columns in the other indexes you create on this table. A wide Clustered Index would expand the size of all of the non-clustered indexes on that table.

Should you make the Primary Key alone be the Clustered Index?

• If you have a small Primary Key (INT or BIGINT) and it is the Clustered Index, the overhead of the cluster columns is relatively small. Although the Clustered Primary Key in this case will also exist in every index on this table, it is a smaller price to pay than the Wide Cluster discussed above.

• This Primary Key Clustered Index will usually not directly offer an easy path to serially selecting many rows.

• Now that you have created a Clustered Primary Key, what about those other columns you were once planning to include in the Clustered Index?

• Create a Unique (or a Non-Unique) index as needed to index that wide search criteria of columns C1, C2, C3, C4, C5. The values in this “Imitation Clustered” Index can serve as a faster search path for those 5 columns. If there is a non-indexed column or two that are regularly selected as well, they can be included in the index with INCLUDE (Doctor_Name, Diagnosis_Synopsis).

Although I find simple Clustered Indexes and Primary Keys useful there are some good reasons for thinking through whether to use them in a table or in a database.

Do you need a Clustered Index at all?

• If you create indexes (Unique Indexes and Non-Unique Indexes) and defining the Primary Key without the overhead of being a Clustered Index, you might find that the narrower indexes provide you what you need for your queries.

• There are some useful behaviors in Clustered Indexes and Primary Keys, but remember that it is really the indexes that matter the most. Design the indexing strategy to take into account the realities of your application. Maybe the OneBigTable needs to have a different indexing strategy from what you use for most of the tables.

• Without a Clustered Index your data will be stored as a heap with the Row Identifier (RID) which is not a good search mechanism at all. But, as mentioned previously, you can create unique and non-unique indexes to handle your queries.

Which now takes you to considering Heaps:

Heaps and Indexes: https://msdn.microsoft.com/en-us/library/hh213609.aspx

• When a table is stored as a heap, individual rows are identified by reference to a row identifier (RID) consisting of the file number, data page number, and slot on the page. The row id is a small and efficient structure. (But it is not an index.)
• Sometimes data architects use heaps when data is always accessed through nonclustered indexes and the RID is smaller than a clustered index key.

But if you also have some 'hot spots' in a big data set, you can also look into another type of index:

Filtered Index: https://msdn.microsoft.com/en-us/library/cc280372.aspx

• A well-designed filtered index improves query performance and execution plan quality because it is smaller than a full-table nonclustered index and has filtered statistics. The filtered statistics are more accurate than full-table statistics because they cover only the rows in the filtered index.

• Filtered indexes have a number of restrictions which are outlined in the link to filtered indexes.

However, if you are interested in thinking about that possibility of skipping Primary Keys and Clustered Indexes altogether, you might read Markus Winand's post linked below. He demonstrates his reasons, with some code samples, to suggest that it might be a good idea at times to forego using those features.

http://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key

But it all finally comes back to understanding your application and designing the code, the tables, the indexes, and so forth to fit the job you are doing.

Answer 4

A couple of points to consider.

While an index (clustered or not) on a monotonously increasing value saves you page splits during mass inserts, it creates a new hot spot at the tail end of the index. Though it may not be a problem with a single thread bulk insert, it will definitely increase contention for a multithreaded application inserting new tuples at a high rate, as the threads will constantly compete for access to the last page of the index.

Clustering the table based on a surrogate (identity) PK is rarely beneficial. Such a primary key is mostly used to either access individual tuples, one at a time, or scan the entire index for joins. In either case it doesn't matter whether the index is clustered or not (with the exception of merge joins, may be, but how frequent are they?)

I think that you will benefit most from a clustered index that covers queries asking for a key range scan and additional predicates referencing other columns.