SQL SERVER – Difference Between Unique Index vs Unique Constraint

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
Previous Post
SQL SERVER – Enable xp_cmdshell using sp_configure
Next Post
SQL SERVER – SELECT vs. SET Performance Comparison

Related Posts

56 Comments. Leave new

  • I think Unique non clustered index will allow you to add columns in the include columns.

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

    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

Leave a Reply