Search This Blog

SQL Server-Primary Keys without clustered indexes


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:
  1. Create a test table without any keys or indexes defined
  2. Define a primary key on the test table, with an explicit NONCLUSTERED keyword added to the definition
  3. Define a CLUSTERED index on the table on a column that’s not the primary key
  4. Check the details of the indexes created using sys.indexes catalog view
01.USE tempdb
02.GO
03. 
04.--Safety Check
05.IF OBJECT_ID('pkTest') IS NOT NULL
06.DROP TABLE pkTest
07.GO
08. 
09.--Create test table definition
10.CREATE TABLE pkTest (Id INT NOT NULL IDENTITY(1,1),
11.DummyId INT NOT NULL,
12.KeyName VARCHAR(20)
13.)
14.GO
15. 
16.--Notice the use of NONCLUSTERED
17.ALTER TABLE pkTest
18.ADD CONSTRAINT pk_pkTest_pkTestId PRIMARY KEY NONCLUSTERED (Id)
19.GO
20. 
21.--Now create a clustered index on a column other than the Primary Key
22.CREATE CLUSTERED INDEX idx_pkTest_DummyId ON pkTest (DummyId)
23.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:
01.--Check the types of Indexes that have been created
02.SELECT si.object_id,
03.OBJECT_NAME(si.object_id) AS ObjectName,
04.si.name,
05.si.index_id,
06.si.type,
07.si.type_desc,
08.si.is_unique,
09.si.is_unique_constraint,
10.si.is_primary_key
11.FROM sys.indexes AS si
12.WHERE si.object_id = OBJECT_ID('pkTest')
13.GO
Query execution yields the following results:
object_idObjectNamenameindex_idtypetype_descis_uniqueis_unique_constraintis_primary_key
853578079pkTestidx_pkTest_DummyId11CLUSTERED000
853578079pkTestpk_pkTest_pkTestId22NONCLUSTERED101

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

Subscribe to: Posts (Atom)