SQL SERVER – Difference Between Unique Index vs Unique Constraint

17 years ago

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)

SQL Constraint and Keys, SQL Index, SQL Scripts

SQL SERVER – Enable xp_cmdshell using sp_configure

SQL SERVER – SELECT vs. SET Performance Comparison

56 Comments. Leave new

karthik
March 13, 2013 2:12 am
How to create a BITMAP Index in sql server 2008R2?
Reply
Ranjith
March 15, 2013 10:57 am
I think Unique non clustered index will allow you to add columns in the include columns.
Reply
Mike
August 4, 2013 9:59 pm
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.
Reply
Mahesh
January 16, 2014 4:05 pm
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?
Reply
- Partha Pratim De
  May 7, 2017 9:04 am
  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
  Reply
  - tp
    January 9, 2018 11:33 am
    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.
Brian Stork
March 31, 2020 9:10 pm
A unique index can also have a “where” clause and a unique constraint cannot.
Reply
Wolfgang Pachur
November 25, 2021 8:31 pm
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.
Reply