A long time ago, I wrote a piece on how primary keys can be designed to use a non-clustered index. The post was quite popular and has some very interesting discussion associated with it. After that post, I have kept my eyes and ears open for myths and misconceptions around the whole primary key-clustered index-unique index combination and was surprised that a lot of novice developers think that all 3 are the same, i.e. a primary key is always a clustered index which is unique and that a unique index is same as a clustered index.
Now, obviously, there is a fundamental understanding issue here. In my earlier post, I broke the link between a primary key and a clustered index. Today, I will provide a few examples that help understand that:
Primary keys are always unique. But, a clustered index may not be unique and a unique index may not be clustered.
Please NOTE that this post is only intended to clear out a concept, and is not intended to be a recommendation or a best practice.
The Demonstration
It goes without saying that if we create a table with a primary key in the “usual” way (i.e. with a clustered index on the primary key), it will be both clustered and unique. Hence, I will address the demo in the following parts:
- First, I will prove that even a non-clustered primary key is unique (it has to be, by definition)
- Next, I will create a clustered Index on a column other than the primary key and prove that it is not unique
- Finally, I will create a non-clustered unique index to prove that unique indexes are not necessarily clustered
Primary Keys are always Unique
The code provided below creates a clustered primary key on a test table and goes on to check the nature of the indexes created by the CREATE TABLE statement.
USE tempdb;
GO–Safety Check
IF OBJECT_ID(‘tblIsClusteredPKUnique’) IS NOT NULL
DROP TABLE tblIsClusteredPKUnique;
GO–Create test table definition
–Notice that we are creating a clustered primary key
CREATE TABLE tblIsClusteredPKUnique (Id INT NOT NULL IDENTITY(1,1),
DummyId INT NOT NULL,
KeyName VARCHAR(20),
CONSTRAINT pk_tblIsClusteredPKUnique_Id
PRIMARY KEY CLUSTERED (Id)
);
GO–Check the types of Indexes that have been created
SELECT si.object_id,
OBJECT_NAME(si.object_id) AS ObjectName,
si.name,
si.index_id,
si.type,
si.type_desc,
si.is_unique,
si.is_unique_constraint,
si.is_primary_key
FROM sys.indexes AS si
WHERE si.object_id = OBJECT_ID(‘tblIsClusteredPKUnique’);
GO
As can be seen from the output from sys.indexes, we can confirm that: The clustered primary key was created as a unique index.
Now, let me create a non-clustered primary key and check the uniqueness of that non-clustered index.
USE tempdb;
GO–Safety Check
IF OBJECT_ID(‘tblIsNonClusteredPKUnique’) IS NOT NULL
DROP TABLE tblIsNonClusteredPKUnique;
GO–Create test table definition
–Notice that the primary key has been defined as NON-CLUSTERED
CREATE TABLE tblIsNonClusteredPKUnique (Id INT NOT NULL IDENTITY(1,1),
DummyId INT NOT NULL,
KeyName VARCHAR(20),
CONSTRAINT pk_tblIsNonClusteredPKUnique_Id
PRIMARY KEY NONCLUSTERED (Id)
);
GO–Check the types of Indexes that have been created
SELECT si.object_id,
OBJECT_NAME(si.object_id) AS ObjectName,
si.name,
si.index_id,
si.type,
si.type_desc,
si.is_unique,
si.is_unique_constraint,
si.is_primary_key
FROM sys.indexes AS si
WHERE si.object_id = OBJECT_ID(‘tblIsNonClusteredPKUnique’);
GO
As can be seen from the output from sys.indexes, we can confirm that: The Primary Key, although non-clustered, was created as a unique index. From an academic perspective, notice that the table was created as a heap, but that is the subject of another discussion.
Clustered Indexes may not be Unique
The code provided below creates a clustered index (without a primary key definition) on a test table and goes on to check the nature of the indexes created by the CREATE TABLE statement.
USE tempdb;
GO–Safety Check
IF OBJECT_ID(‘tblIsClusteredIndexUnique’) IS NOT NULL
DROP TABLE tblIsClusteredIndexUnique;
GO–Create test table definition
–Notice that NO primary key has been defined
CREATE TABLE tblIsClusteredIndexUnique (Id INT NOT NULL IDENTITY(1,1),
DummyId INT NOT NULL,
KeyName VARCHAR(20)
);
GO–Now create a clustered index on a column other than the Primary Key
CREATE CLUSTERED INDEX idx_tblIsClusteredIndexUnique_DummyId
ON tblIsClusteredIndexUnique (DummyId);
GO–Check the types of Indexes that have been created
SELECT si.object_id,
OBJECT_NAME(si.object_id) AS ObjectName,
si.name,
si.index_id,
si.type,
si.type_desc,
si.is_unique,
si.is_unique_constraint,
si.is_primary_key
FROM sys.indexes AS si
WHERE si.object_id = OBJECT_ID(‘tblIsClusteredIndexUnique’)
GO
As can be seen from the output from sys.indexes, we can confirm that: The clustered index was NOT created as a unique index by default.
Unique Indexes may not be Clustered
The code provided below creates a unique, non-clustered index on a test table and goes on to check the nature of the indexes created by the CREATE TABLE statement.
USE tempdb
GO–Safety Check
IF OBJECT_ID(‘tblIsUniqueIndexClustered’) IS NOT NULL
DROP TABLE tblIsUniqueIndexClustered
GO–Create test table definition
–Notice that NO primary key (clustered or otherwise) has been defined
CREATE TABLE tblIsUniqueIndexClustered (Id INT NOT NULL IDENTITY(1,1),
DummyId INT NOT NULL,
KeyName VARCHAR(20)
)
GO–Now create a clustered index on a column other than the Primary Key
CREATE UNIQUE NONCLUSTERED INDEX idx_tblIsUniqueIndexClustered_DummyId
ON tblIsUniqueIndexClustered (DummyId);
GO–Check the types of Indexes that have been created
SELECT si.object_id,
OBJECT_NAME(si.object_id) AS ObjectName,
si.name,
si.index_id,
si.type,
si.type_desc,
si.is_unique,
si.is_unique_constraint,
si.is_primary_key
FROM sys.indexes AS si
WHERE si.object_id = OBJECT_ID(‘tblIsUniqueIndexClustered’);
GO
As can be seen from the output from sys.indexes, we can confirm that: The non-clustered index was created as a unique index due to the use of the UNIQUE keyword.
Conclusion
In conclusion, I would present the following points:
- A primary key is always unique
- A primary key may or may not be clustered
- A clustered index may not be unique
- A unique index may not be clustered
- The UNIQUE keyword is essential for defining an index as unique, unless it is an index created as a result of the primary key definition
Further Reading
- Primary keys may not be clustered [Link]
- Clustered Index Design Recommendations [Link]
- sys.indexes [Link]
Until we meet next time,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 