The Primary Key vs. The Clustered Index

Jon Seigel

When it comes to clustered vs. non-clustered indexes in SQL Server, there’s still a lot of confusion with those types of indexes, and constraints, particularly the primary key. Frankly, that’s entirely understandable, because it is a bit confusing.

Let me try to simplify.

First, there are two ways indexes can be created:

  1. Directly (i.e., CREATE … INDEX).
  2. Certain types of constraints (UNIQUE, and PRIMARY KEY) create indexes to implement the constraint.

The behind-the-scenes constraint indexes appear in both Management Studio and sys.indexes, which make the waters a bit muddy, so to speak. This is also how there is spillover in terminology and statement syntax between indexes and constraints.

The key to understanding how this works is to separate the ideas of constraints and indexes.

In theory, the constraints I mentioned above could be implemented behind the scenes using another type of structure completely different than an index. It just so happens that an index is an available, reusable, efficient structure that is able to get the job done.

Second, there is a default type of index created by each type of constraint, and the default can be overridden:

  • A PRIMARY KEY constraint creates a clustered index by default.
  • A UNIQUE constraint creates a non-clustered index by default.

Here are all the combinations, and the type of index that gets created:

/* Clustered index */
ALTER TABLE MyTable
	ADD CONSTRAINT PK_MyTable
		PRIMARY KEY(WidgetId);

/* Clustered index */	
ALTER TABLE MyTable
	ADD CONSTRAINT PK_MyTable
		PRIMARY KEY CLUSTERED(WidgetId);

/* Non-clustered index */	
ALTER TABLE MyTable
	ADD CONSTRAINT PK_MyTable
		PRIMARY KEY NONCLUSTERED(WidgetId);


/* Non-clustered index */	
ALTER TABLE MyTable
	ADD CONSTRAINT UC_MyTable_WidgetNumber
		UNIQUE(WidgetNumber);

/* Non-clustered index */	
ALTER TABLE MyTable
	ADD CONSTRAINT UC_MyTable_WidgetNumber
		UNIQUE NONCLUSTERED(WidgetNumber);

/* Clustered index */	
ALTER TABLE MyTable
	ADD CONSTRAINT UC_MyTable_WidgetNumber
		UNIQUE CLUSTERED(WidgetNumber);

Finally, there is debate about whether a simple index, or a constraint should be created, because in a lot of cases, they can function identically with respect to user queries. There are several factors that come into play when making this decision:

  • Constraints have extra restrictions on them (i.e., columns in a primary key constraint cannot allow NULLs).
  • Creating a constraint instead of an index may serve to better document data/business rules in the database.
  • Constraints take a little more effort to drop, which reduces the likelihood of a mistake.
  • It may not be possible to represent a data/business rule using a constraint, while it is possible using a normal index. Constraint indexes can’t use special features such as filters, for example.
  • Would creating an index with the same columns as a data/business rule better serve user queries? Constraint indexes don’t allow included columns. If the table is large, creating and maintaining two separate indexes, one of which may be essentially redundant, can be expensive.

My personal philosophy is to first use constraints to maintain data/business rules, and then supplement with regular indexes as necessary. Constraints alone are frequently enough to satisfy user queries; however, there are definitely situations where it’s clear that methodology should be broken. It’s very situational, and really there’s no better person qualified to evaluate the situation than you.

Summary:

  • Indexes and constraints are separate objects.
  • Be aware of the default types of indexes created by the different types of constraints.
  • Create either constraints and/or indexes based on your individual needs.