When working with SQL Server, it is important to understand the concepts of Primary Key and Clustered Index. These two terms are often used interchangeably, but they have distinct meanings and purposes.

First, let’s clarify the common misconception that Primary Key has to be a Clustered Index. In reality, a Primary Key can be either a Clustered or Non-clustered Index. However, it is considered a best practice to create a Primary Key as a Clustered Index.

A Primary Key is a column or set of columns that uniquely identifies each row in a table. It should be NOT NULL, meaning it cannot contain null values. A good candidate for a clustered index key is a column that also uniquely identifies each row and is NOT NULL.

By default, SQL Server automatically creates a Clustered Index on the Primary Key when a table is created. This is why many developers assume that Primary Key and Clustered Index are the same thing. However, they can be the same column, but they do not have to be.

Let’s explore four scenarios to understand the behavior of SQL Server when it comes to Primary Key and Clustered Index:

Scenario 1: Primary Key will default to Clustered Index

In this scenario, we create a table with only a Primary Key. When we check the indexes on the table, we will notice that SQL Server has automatically created a clustered index on the Primary Key.

-- Case 1: Primary Key Defaults to Clustered Index
USE TempDB
GO

-- Create table
CREATE TABLE TestTable (
    ID INT NOT NULL PRIMARY KEY,
    Col1 INT NOT NULL
)
GO

-- Check Indexes
SELECT OBJECT_NAME(OBJECT_ID) AS TableObject, [name] AS IndexName, [Type_Desc]
FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'TestTable'
GO

-- Clean up
DROP TABLE TestTable

Scenario 2: Primary Key is defined as a Non-clustered Index

In this scenario, we explicitly define the Primary Key as a non-clustered index. SQL Server will create it as a non-clustered index, proving that a Primary Key can be a non-clustered index.

-- Case 2: Primary Key Non-clustered Index
USE TempDB
GO

-- Create table
CREATE TABLE TestTable (
    ID INT NOT NULL PRIMARY KEY NONCLUSTERED,
    Col1 INT NOT NULL
)
GO

-- Check Indexes
SELECT OBJECT_NAME(OBJECT_ID) AS TableObject, [name] AS IndexName, [Type_Desc]
FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'TestTable'
GO

-- Clean up
DROP TABLE TestTable

Scenario 3: Primary Key defaults to Non-Clustered Index with another column defined as a Clustered Index

In this scenario, we create a clustered index on another column. SQL Server will automatically create the Primary Key as a non-clustered index, as a clustered index is specified on another column.

-- Case 3: Primary Key Defaults to Non-clustered Index
USE TempDB
GO

-- Create table
CREATE TABLE TestTable (
    ID INT NOT NULL PRIMARY KEY,
    Col1 INT NOT NULL UNIQUE CLUSTERED
)
GO

-- Check Indexes
SELECT OBJECT_NAME(OBJECT_ID) AS TableObject, [name] AS IndexName, [Type_Desc]
FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'TestTable'
GO

-- Clean up
DROP TABLE TestTable

Scenario 4: Primary Key defaults to Clustered Index with other index defaults to Non-clustered index

In this scenario, we create two indexes on the table without specifying the type of index on the columns. When we check the results, we will notice that the Primary Key is automatically defaulted to a Clustered Index, while the other column has a Non-clustered Index.

-- Case 4: Primary Key and Defaults
USE TempDB
GO

-- Create table
CREATE TABLE TestTable (
    ID INT NOT NULL PRIMARY KEY,
    Col1 INT NOT NULL UNIQUE
)
GO

-- Check Indexes
SELECT OBJECT_NAME(OBJECT_ID) AS TableObject, [name] AS IndexName, [Type_Desc]
FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'TestTable'
GO

-- Clean up
DROP TABLE TestTable

These examples clarify the relationship between Primary Key and Clustered Index. It is important to note that there are scenarios where it may be necessary to create a Primary Key and Clustered Index on different columns. For example, you may have a column like SSN that you want to create as a Primary Key, but you do not want to make it a clustered index key because you have another uniquely increasing identity column that better suits your needs for that table.

If you have any further questions or would like to continue the discussion on this topic, please feel free to leave a comment below or refer to a dedicated blog post I wrote about it years ago.