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)./p>
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.|