One of my favourite interview questions at an intermediate level has always been: “Can I have a primary key on a table that does not contribute to the clustered index?”. Of course, it’s not always the same question – there are multiple variants, such as :
- Can a table have a primary key that does not participate in a clustered index?
- Does designating a column(s) as a primary key automatically create a clustered index on the table based on the key column(s)?
- Can a clustered index be created using a column(s) other than the primary key of the table?
The answer that most candidates come up with is that there is a direct dependency between the primary key and the clustered index – the primary key has to be the set of columns on which one wishes to create a clustered index.
Contrary to popular belief, the correct answer is “Yes, a table may have a clustered index defined on a column other than the primary key of the table”. It’s just that most developers are so used to the default design and coding mechanisms that generally the thought of going against the flow may not come to the mind.
Please note that this post is not about the recommended practices for creation of clustered index and primary keys, but is intended to achieve an understanding of the fact that having a primary key does not automatically imply a clustered index in all situations.
Demo
As supporting proof, allow me to create a simple scenario. As part of the demonstration, I would be executing the following steps:
- Create a test table without any keys or indexes defined
- Define a primary key on the test table, with an explicit NONCLUSTERED keyword added to the definition
- Define a CLUSTERED index on the table on a column that’s not the primary key
- Check the details of the indexes created using sys.indexes catalog view
USE tempdb
GO
--Safety Check
IF OBJECT_ID('pkTest') IS NOT NULL
DROP TABLE pkTest
GO
--Create test table definition
CREATE TABLE pkTest (Id INT NOT NULL IDENTITY(1,1),
DummyId INT NOT NULL,
KeyName VARCHAR(20)
)
GO
--Notice the use of NONCLUSTERED
ALTER TABLE pkTest
ADD CONSTRAINT pk_pkTest_pkTestId PRIMARY KEY NONCLUSTERED (Id)
GO
--Now create a clustered index on a column other than the Primary Key
CREATE CLUSTERED INDEX idx_pkTest_DummyId ON pkTest (DummyId)
GO
As you can see, we created the Primary Key with a keyword – NONCLUSTERED. This should create a non-clustered index for the primary key and a clustered index for the DummyId column. Let’s run the following query to check:
--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('pkTest')
GO
Query execution yields the following results:
| object_id | ObjectName | name | index_id | type | type_desc | is_unique | is_unique_constraint | is_primary_key |
| 853578079 | pkTest | idx_pkTest_DummyId | 1 | 1 | CLUSTERED | 0 | 0 | 0 |
| 853578079 | pkTest | pk_pkTest_pkTestId | 2 | 2 | NONCLUSTERED | 1 | 0 | 1 |
Conclusion
A clustered index can be defined on any column of a table as long as the column satisfies the criteria for a clustered index. Having a primary key does not imply that clustered index will exist on the primary key under all situations.
Reference
- Clustered Index Design Guidelines (BOL/MSDN): http://msdn.microsoft.com/en-us/library/ms190639(v=sql.105).aspx
Until we meet next time,
SQLTwins by Nakul Vachhrajani 