Unique Index and Unique Constraint are the same. They achieve same goal. SQL Performance is same for both.
Add Unique Constraint
ALTER TABLE dbo.<tablename> ADD CONSTRAINT
<namingconventionconstraint> UNIQUE NONCLUSTERED
(
<columnname>
) ON [PRIMARY]
Add Unique Index
CREATE UNIQUE NONCLUSTERED INDEX
<namingconventionconstraint> ON dbo.<tablename>
(
<columnname>
) ON [PRIMARY]
There is no difference between Unique Index and Unique Constraint. Even though syntax are different the effect is the same. Unique Constraint creates Unique Index to maintain the constraint to prevent duplicate keys. Unique Index or Primary Key Index are physical structure that maintain uniqueness over some combination of columns across all rows of a table. It is a convenient way to enforce a Unique Constraint for SQL Server.
Reference : Pinal Dave (https://blog.sqlauthority.com)
56 Comments. Leave new
I think Unique non clustered index will allow you to add columns in the include columns.
I realize this is an old thread but wouldn’t an unique index also potentially participate in a query plan where as a constraint would only be used to check on inserts/updates? My guess is statistics and such would help performance of the index version and keep it in memory where as the constraint version wouldn’t necessarily live in ram at startup/move in and out similar to a “hot” index being hit by lots of queries.
There is no difference between Unique Index and Unique Constraint. So, My question is if unique key column allow single “NULL” values insertion then is it possible to insert single “NULL” values inside unique indexed column?
You could allow multiple NULLs in Unique index, with a where condition. Whereas in UNIQUE constraint you cannot do that.
create table num (a int)
go
— unique constraint
alter table num add constraint unique_a unique(a)
— insert nulls
insert into num values (null) — works
insert into num values (null) — doesn’t work
alter table num drop constraint unique_a
— unique index — with where condition
create unique index u_idx on num (a) WHERE a IS NOT NULL
— insert nulls
insert into num values (null) — works
insert into num values (null) — works
I tried to insert sing where condition its not working. create unique index u_idx on num (a) WHERE a IS NOT NULL
I case of create unique index u_idx on num (a) WHERE a IS NOT NULL insert into num values (null) — doesn’t work ——-This Statment works.
A unique index can also have a “where” clause and a unique constraint cannot.
I encountered a different behavior this day.The mere index on a char column did prevent a multiple insert. But in SSMS it wrote only a message telling about *ignoring*, and it did *not* issue an error. The UNIQUE CONSTRAINT in contrast did so.
This is a huge difference in working with our frontend application when displaying errors or handling them. So the application told the user “all inserts ok” when in fact there was no insertion. By applying the constraint it displayed the SQL Server error message, as expected.