Unique Key is Clustered or Nonclustered

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
(
ID INT,
Name VARCHAR(100)
)
GO

//Create Unique key constraint
ALTER TABLE tbl_UniqueKeyDemo
ADD CONSTRAINT UK_tbl_UniqueKeyDemo_ID UNIQUE(ID)

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

//Create table
CREATE TABLE tbl_UniqueKeyDemo
(
ID INT UNIQUE,
Name VARCHAR(100)
)
GO

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

 Conclusion

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.