Is Unique key Clustered or Non-Clustered Index in SQL Server?

Question

I am new to SQL Server and while learning about clustered index, I got confused!

Is unique key clustered or a non-clustered index? Unique key holds only unique values in the column including null, so according to this concept, unique key should be a clustered index, right? But when I went through this article I got confused MSDN

When you create a UNIQUE constraint, a unique nonclustered index is created to enforce a UNIQUE constraint by default. You can specify a unique clustered index if a clustered index on the table does not already exist.

Please help me to understand the concept in a better manner, Thank you.

Answer 1

There are three ways of enforcing uniqueness in SQL Server indexes.

• Primary Key constraint
• Unique constraint
• Unique index (not constraint based)

Whether they are clustered or non clustered is orthogonal to whether or not the indexes are declared as unique using any of these methods.

All three methods can create a clustered or non clustered index.

By default the unique constraint and Unique index will create a non clustered index if you don't specify any different (and the PK will by default be created as CLUSTERED if no conflicting clustered index exists) but you can explicitly specify CLUSTERED/NONCLUSTERED for any of them.

Example syntax is

CREATE TABLE T
(
X INT NOT NULL,
Y INT NOT NULL,
Z INT NOT NULL
);

ALTER TABLE T ADD PRIMARY KEY NONCLUSTERED(X);

--Unique constraint NONCLUSTERED would be the default anyway
ALTER TABLE T ADD UNIQUE NONCLUSTERED(Y); 

CREATE UNIQUE CLUSTERED INDEX ix ON T(Z);

DROP TABLE T;

For indexes that are not specified as unique SQL Server will silently make them unique any way. For clustered indexes this is done by appending a uniquefier to duplicate keys. For non clustered indexes the row identifier (logical or physical) is added to the key to guarantee uniqueness.