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. |