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.

Viscid

A unique key is not clustered, unless you explicitly define it using the "clustered" option on create index. – Goodard

It's explained a bit better in #1252136 and msdn.microsoft.com/en-CA/library/ms190457.aspx. – Fleshly

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.

Wassail

could you guide me with some references on how to create a primary & unique key with clustered / non-clustered index. If primary key can be a non-clustered index then does it mean that we can have more than one primary key in a table, how can we do that? please could you provide me with some details, the more i tend to learn about this thing the more i tend to get confused.. Thank you – Viscid

@LijinJohn - You can only have one primary key per table and you can only have one clustered index per table but these don't have to be the same thing. – Wassail

what i tend to learn from msdn blogs is that there is no data related difference between a Unique key and a Unique constraints, am i right? – Viscid

@LijinJohn - No there's no difference except in terms of flexibility. A unique index can have included columns defined. A constraint based index cant. – Wassail

Thank you very much for your support :) – Viscid

Answer 2

Unique index can be both clustered or non-clustered. But if you have nullable column the NULL value should be unique (only 1 row where column is null). If you want to store more then 1 NULLs you can create the index with filter "where columnName is not null".

Crossley

Answer 3

well all the answers provided was very helpful, but still i would like to add some detailed answer so that i would be helpful for some others as well

1. A table can contain only one clustered index and a primary key can be a clustered / non-clustered index.
2. Unique Key can be a clustered/non-clustered index as well, below are some of the examples

Scenario 1 : Primary Key will default to Clustered Index

In this case we will create only Primary Key and when we check the kind of index created on the table we will notice that it has created clustered index automatically over it.

USE TempDB
GO
-- Create table
CREATE TABLE TestTable
(ID INT NOT NULL PRIMARY KEY,
Col1 INT NOT NULL)
GO
-- Check Indexes
SELECT OBJECT_NAME(OBJECT_ID) TableObject,
[name] IndexName,
[Type_Desc] FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'TestTable'
GO
-- Clean up
DROP TABLE TestTable
GO

Scenario 2: Primary Key is defined as a Non-clustered Index

In this case we will explicitly defined Primary Key as a non-clustered index and it will create it as a non-clustered index. It proves that Primary Key can be non-clustered index.

USE TempDB
GO
-- Create table
CREATE TABLE TestTable
(ID INT NOT NULL PRIMARY KEY NONCLUSTERED,
Col1 INT NOT NULL)
GO
-- Check Indexes
SELECT OBJECT_NAME(OBJECT_ID) TableObject,
[name] IndexName,
[Type_Desc] FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'TestTable'
GO
-- Clean up
DROP TABLE TestTable
GO

Scenario 3: Primary Key defaults to Non-Clustered Index with another column defined as a Clustered Index

In this case we will create clustered index on another column, SQL Server will automatically create a Primary Key as a non-clustered index as clustered index is specified on another column.

-- Case 3 Primary Key Defaults to Non-clustered Index
USE TempDB
GO
-- Create table
CREATE TABLE TestTable
(ID INT NOT NULL PRIMARY KEY,
Col1 INT NOT NULL UNIQUE CLUSTERED)
GO
-- Check Indexes
SELECT OBJECT_NAME(OBJECT_ID) TableObject,
[name] IndexName,
[Type_Desc] FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'TestTable'
GO
-- Clean up
DROP TABLE TestTable
GO

Scenario 4: Primary Key defaults to Clustered Index with other index defaults to Non-clustered index

In this case we will create two indexes on the both the tables but we will not specify the type of the index on the columns. When we check the results we will notice that Primary Key is automatically defaulted to Clustered Index and another column as a Non-clustered index.

-- Case 4 Primary Key and Defaults
USE TempDB
GO
-- Create table
CREATE TABLE TestTable
(ID INT NOT NULL PRIMARY KEY,
Col1 INT NOT NULL UNIQUE)
GO
-- Check Indexes
SELECT OBJECT_NAME(OBJECT_ID) TableObject,
[name] IndexName,
[Type_Desc] FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'TestTable'
GO
-- Clean up
DROP TABLE TestTable
GO

reference:the above details is been refrenced from this article

Viscid