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

Gopal is a passionate Data Engineer and Data Analyst. He has implemented many end to end solutions using Big Data, Machine Learning, OLAP, OLTP, and cloud technologies. He loves to share his experience at https://www.sqlrelease.com/. Connect with Gopal on LinkedIn at https://www.linkedin.com/in/ergkranjan/.

Leave a comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

2 thoughts on “Unique Key is Clustered or Nonclustered