What is the difference between a unique constraint and a unique index

Question

What is the difference between the following two statements?

alter table [dbo].[Demo] add constraint [UC_Demo_Code] unique ( [Code] )
go
create unique nonclustered index [UK_Demo_Code] on [dbo].[Demo] ( [NB_Code] )
go

Does the constraint have an index to help it enforce uniqueness, or will a table scan be performed on every insert/update?

Answer 1

You can Check Unique Constraints and Unique Indexes for a comparison between them.

The article concludes that there's no practical difference between a unique constraint and a unique index other than the fact that the unique constraint is also listed as a constraint object in the database. Since a unique constraint can't be disabled, having the status of a constraint doesn't give the unique constraint any additional behavior beyond a unique index. However, there are several index creation options that aren't available to the ALTER TABLE command that creates a unique constraint.

Answer 2

The "logical" effect is the same -- only unique values may be loaded into the table. (It's worth mentioning that if the column is nullable, only 1 row with NULL may be inserted.)

The physical effect is the same -- a unique index is built on the table. It could be either clustered or nonclustered.

The only real difference is in the metadata, the information describing the database as stored in the system tables. The first way, it is recorded internally as an index, and the second, it is recorded as a constraint -- even though the net effects are identical. Which means that, ultimately, the only difference is the code required to create it AND to alter it in the future.

(This is true for SQL 7.0 through 2005, and I'd be very surprised if they changed it in 2008).