Unique Index

Introduction

A unique index serves a dual purpose: it provides the performance benefits of a regular index AND enforces a business rule — no two rows can have the same value in the indexed column(s). It is one of the most practical index types you will use.

In this tutorial, you will learn:

What a unique index is and how it differs from a regular index
The relationship between unique indexes and constraints
How to create unique indexes
How unique indexes handle NULL values
When and why to use unique indexes

What is a Unique Index?

A unique index guarantees that no two rows in the table can have the same value (or combination of values) in the indexed column(s). If someone tries to insert or update a row that would create a duplicate, the database rejects the operation with an error.

Unique Index vs UNIQUE Constraint

There are two ways to enforce uniqueness in SQL Server. They are functionally almost identical:

Method 1: CREATE UNIQUE INDEX

-- Using CREATE UNIQUE INDEX
CREATE UNIQUE NONCLUSTERED INDEX UX_Customers_Email
ON customers (email);

Method 2: UNIQUE Constraint

-- Using a UNIQUE CONSTRAINT (creates a unique index behind the scenes)
ALTER TABLE customers
ADD CONSTRAINT UQ_Customers_Email UNIQUE (email);

Comparison

Feature	UNIQUE INDEX	UNIQUE CONSTRAINT
Enforces uniqueness	Yes	Yes
Creates an index	Yes (you define it)	Yes (auto-created behind the scenes)
INCLUDE columns	Yes	No
Filtered	Yes (WHERE clause)	No
Referenced by FK	No	Yes
Shows in constraints	No	Yes
Flexibility	More options	Simpler syntax

When to Use Which

Scenario	Recommendation
Need a foreign key reference	Use UNIQUE CONSTRAINT
Need INCLUDE columns or filtering	Use UNIQUE INDEX
Simple uniqueness enforcement	Either works
Need the index to be self-documenting as a constraint	Use UNIQUE CONSTRAINT

Diagram showing how unique indexes enforce uniqueness and the difference between unique index and unique constraint

Creating Unique Indexes

Single Column Unique Index

-- Ensure each customer has a unique email
CREATE UNIQUE NONCLUSTERED INDEX UX_Customers_Email
ON customers (email);

Composite Unique Index

-- Ensure each product appears only once per order
CREATE UNIQUE NONCLUSTERED INDEX UX_OrderItems_Order_Product
ON order_items (order_id, product_id);

Unique Index with INCLUDE

-- Unique on email, but also include name for covering
CREATE UNIQUE NONCLUSTERED INDEX UX_Customers_Email_Cover
ON customers (email)
INCLUDE (customer_city, customer_state);

Unique Clustered Index

-- A unique clustered index (this is what PRIMARY KEY creates by default)
CREATE UNIQUE CLUSTERED INDEX UCX_Orders_OrderID
ON orders (order_id);

NULL Handling in Unique Indexes

In SQL Server, a unique index treats NULL as a value for uniqueness purposes. This means only one NULL is allowed in a unique index column.

Workaround: Allow Multiple NULLs

If you need to allow multiple NULL values while enforcing uniqueness for non-NULL values, use a filtered unique index:

-- Allow multiple NULLs, but enforce uniqueness for non-NULL values
CREATE UNIQUE NONCLUSTERED INDEX UX_Customers_Phone
ON customers (phone_number)
WHERE phone_number IS NOT NULL;

Summary

Here is what you should remember about unique indexes:

Concept	Key Takeaway
What	An index that prevents duplicate values in the indexed column(s)
Dual purpose	Performance improvement + data integrity enforcement
NULL handling	Only one NULL allowed (in SQL Server)
vs Constraint	More flexible (supports INCLUDE, WHERE) but not FK-referenceable
Primary Key	Automatically creates a unique clustered index
Multiple NULLs	Use a filtered unique index to allow them

Common Use Cases

Column	Why Unique
Email address	Each customer must have a distinct email
Username	No two users should share a username
Order + Product combo	Each product appears once per order
Employee ID	Natural business identifier
Social Security Number	Government-issued unique identifier

In the next tutorial, you will learn about Filtered Indexes — indexes that only cover a subset of rows.