SQL UNIQUE Vs UNIQUE INDEX Constraint

We'll talk about unique indexes and limitations in this session. The terms "UNIQUE INDEX" and "UNIQUE CONSTRAINT" are interchangeable.

They both accomplish the same aim. Both have the same SQL performance. Both ensure that the column is distinct.

SQL UNIQUE CONSTRAINT

When we define a UNIQUE CONSTRAINT on a column, SQL Server produces a unique index on that column to maintain the constraint and prevent duplicate keys.

Example: Creating unique constraint:

ALTER TABLE Student
ADD CONSTRAINT UC_Student_NewValue
UNIQUE (NewValue)
GO

SQL UNIQUE INDEX Constraint

A UNIQUE INDEX , also known as a primary key index, is a physical structure that ensures uniqueness across all rows of a table.

It's a simple technique to enforce a SQL Server Unique Constraints. A unique index on the column is automatically produced when a unique constraint is created (s).

Example: UNIQUE index

CREATE UNIQUE INDEX UIX_Student_Value ON Student(Value)
GO

SQL Difference Between UNIQUE And UNIQUE INDEX Constraint

In below table we will distinguish between unique Vs unique index Constriant:-

UNIQUE CONSTRAINT	UNIQUE INDEX
We have a Filtered index and include columns that cannot be defined on the index that is produced when the Unique constraint is defined.	Include columns that can be defined while defining Unique Index when we have Filtered index.
Non clustered indexes are always created when a unique constraint is applied.	Clustered or non-clustered unique indexes are available.
By including columns in a Unique constraint, we can't avoid Key lookup.	By including columns in a Unique Index, we may prevent key lookup.

All Tutorials