Unique Key is Clustered or Nonclustered 2

An easy question for you “Unique Key is Clustered or Nonclustered”? Lets explain in more detail, When I create a Unique key on a table without explicitly defining the type of index(clustered or nonclustered), which type of index will be used to accommodate the key?

I have already written a blog on behavior of primary key for clustered and nonclustered index type selection in such kind of scenarios and here is the link. In this post titled as “Unique Key is Clustered or Nonclustered”, we will explore the behavior of Unique Key in regards of Clustered and Nonclustered index type selection. Before going forward, I would like to focus on the differences between Primary Key and Uniquer Key below.

Difference between Primary Key and Unique Key

 Primary key

  1. It is a column or set of columns, used to uniquely identify each row of the given table.
  2. NULL values are not allowed.
  3. There can be only one primary key on a table.
  4. By default, creates a clustered index if the table is a heap table.

Unique Key

  1. A column or set of columns used to identify each row uniquely similar to primary key.
  2. Can entertain one NULL value in a table.
  3. There can be multiple Unique Keys on a table
  4. By default, creates a nonclustered index if the table is a heap table.

Unique Key creates Nonclustered index by default

So lets play with demo to prove the last point of above comparison.

//Create table
CREATE TABLE tbl_UniqueKeyDemo
Name VARCHAR(100)

//Create Unique key constraint
ALTER TABLE tbl_UniqueKeyDemo

We can also create this Unique key at the time of tale creation as below:

//Create table
CREATE TABLE tbl_UniqueKeyDemo
Name VARCHAR(100)


Have a look on the below image which ensures that a Unique key creates a nonclustered index on the table by default.

Unique Key is Clustered or Nonclustered


In this post we have demonstrated the default behavior of Unique key in index type (clustered or nonclustered) selection. I have also focused on the differences between primary key and unique key.

Rate This
[Total: 0    Average: 0/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

I am Gopal Krishna Ranjan, having 8 years of industry experience in Software development. I have a head down experience in Database, Data Warehouse, Big Data and cloud technologies and have implemented end to end Database, Data Warehouse,  Big Data and Cloud Solutions. I have extensively worked on SQL Server, Python, Hadoop, Hive, Spark, Azure, Machine Learning, and MSBI (SSAS, SSIS, and SSRS). I also have good experience in windows and web application development using ASP.Net and C#.

Leave a Reply to Gopal Krishna Ranjan Cancel reply

Your email address will not be published. Required fields are marked *

2 thoughts on “Unique Key is Clustered or Nonclustered