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

Excuse me for my first incorrect answer. Unique constraint is identical to unique index. Under the covers, it does create an index.

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