Advertisements
Home > Misconception > SQL Myth | Primary Key (PK) always creates Clustered Index

SQL Myth | Primary Key (PK) always creates Clustered Index


… and this a default nature, which can be overrided to create a PK with a Non-Clustered Index instead of a Clustered one.

– What happens when a Primary Key is created on a table?
– If I have a PK on a table will the table be a Heap or not?
– Can I create a PK with a Non-Clustered Index? (this is a big hint)

While Interviewing candidates I’ve confronted them with these type of question, and very few were able to answer these correctly.

–> So, here we will see the default behavior of Primary Keys and how we can override it:

–> Having the “PRIMARY KEY” option inline with the column name: While creating a new table when you specify a “PRIMARY KEY” option inline with the Key Column, by-default it creates a Clustered Index on that table with a PK Constraint on that column.

USE [tempdb]
GO

CREATE TABLE dbo.Employee (
	EmpID		 INT NOT NULL PRIMARY KEY, -- here
	EmpLogInID 	 VARCHAR(255) NULL,
	EmpFirstName VARCHAR(255) NOT NULL,
	EmpLastName	 VARCHAR(255) NOT NULL,
	Gender		 BIT NOT NULL,
	JobTitle	 VARCHAR(255) NULL,
	BOD			 DATETIME NULL,
	DOJ			 DATETIME NULL,
	DeptID		 INT NOT NULL
)
GO

sp_help 'dbo.Employee'
GO

PK_ByDefault
 
The above image shows:
– a Unique Clustered Index created on the [EmpID] column with a name automatically suggested by the DB-engine, and
– a PK Constraint created on the same column.
 


 
–> Overriding this behavior by having a “CONSTRAINT” option: Here we will not create the PK inline with the Key Column [EmpID]. But we will:
– have a separate PK constraint created with Non-Clustered Index for [EmpID] column, and
– an another SQL statement to create a Clustered Index on the [EmpLogInID] column.

DROP TABLE dbo.Employee
GO

CREATE TABLE dbo.Employee (
	EmpID		 INT NOT NULL,
	EmpLogInID 	 VARCHAR(255) NULL,
	EmpFirstName VARCHAR(255) NOT NULL,
	EmpLastName	 VARCHAR(255) NOT NULL,
	Gender		 BIT NOT NULL,
	JobTitle	 VARCHAR(255) NULL,
	BOD			 DATETIME NULL,
	DOJ			 DATETIME NULL,
	DeptID		 INT NOT NULL
	CONSTRAINT [PK_Employee_EmpID] PRIMARY KEY NONCLUSTERED (EmpID ASC) -- here
)
GO

-- Creating the Clustered Index separately on an other column:
CREATE CLUSTERED INDEX [CI_Employee_EmpLogInID] ON dbo.Employee(EmpLogInID ASC)
GO

sp_help 'dbo.Employee'
GO

PK_Override
 
The above image shows:
– a Clustered Index (Non-Unique) created on the [EmpLogInID] column with a name we provided,
– a Non-Clustered Index (Unique) created on the [EmpID] column, and
– a PK Constraint created on the [EmpID] column with a name we provided.
 

So, it is advised to choose your PK & Clustered/Non-Clustered index wisely based upon a proper and justified Business logic. Please do not consider this as a Use Case, but just an example on how to deal with PKs & Indexes.
 

Check the video on Primary Keys:

PK Constraint


Advertisements
  1. May 24, 2015 at 12:43 pm

    Nice write up Manoj!!!

    This Myth is very famous exists for long time, And I wrote on the same sometimes back as well….

    https://latheeshnk.wordpress.com/2015/02/19/myth-primary-key-and-clustered-index-2/

    • May 24, 2015 at 12:51 pm

      Hi @Latheekesh, thanks for your comments.

      I’ve visited your blog earlier too, and really enjoy reading your posts!!!

  1. November 3, 2015 at 10:20 pm
  2. July 7, 2015 at 2:03 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: