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
(
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.

Unique Key is Clustered or Nonclustered

 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.

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


Gopal Krishna Ranjan

About Gopal Krishna Ranjan

I am Gopal Krishna Ranjan, having 6+ years of industry experience in Software development using Microsoft technologies. I have a head down experience in Database development, performance tuning in SQL Server, T-SQL optimization, BI (Business Intelligence) project implementation, reporting in SSRS, using SSIS for ETL, implementing multi dimensional and tabular data-warehouse in SSAS, querying cubes using MDX and DAX, Windows and Web Applications development with C#.


Leave a Reply to SQLGuy Cancel reply

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

*

2 thoughts on “Unique Key is Clustered or Nonclustered