Does making a column unique force an index to be created?

Question

In SQL Server 2005+ (I use both), does adding the UNIQUE constraint to a column automatically create an index, or should I still CREATE INDEX?

Answer 1

See this MSDN article:

The Database Engine automatically creates a UNIQUE index to enforce the uniqueness requirement of the UNIQUE constraint.

If you do create an index, you'll end up with two indexes, as this example demonstrates:

create table TestTable (id int)
alter table TestTable add constraint unique_id unique (id)
create unique index ix_TestTable_id on TestTable (id)

select * from sys.indexes where [object_id] = object_id('TestTable')

This will display two unique indexes on TestTable; and the HEAP that represents the table itself.

Answer 2

An index is created when you add a unique constraint:

Reference -- see the second paragraph.

When a UNIQUE constraint is added to an existing column or columns in the table, by default, the Database Engine examines the existing data in the columns to make sure all values are unique. If a UNIQUE constraint is added to a column that has duplicated values, the Database Engine returns an error and does not add the constraint.

The Database Engine automatically creates a UNIQUE index to enforce the uniqueness requirement of the UNIQUE constraint. Therefore, if an attempt to insert a duplicate row is made, the Database Engine returns an error message that states the UNIQUE constraint has been violated and does not add the row to the table. Unless a clustered index is explicitly specified, a unique, nonclustered index is created by default to enforce the UNIQUE constraint.

Answer 3

Yes, it does.

In fact, you can even create a CLUSTERED UNIQUE CONSTRAINT:

ALTER TABLE mytable ADD CONSTRAINT UX_mytable_col1 UNIQUE CLUSTERED (col1)

, which will make the table to be clustered on col1.

Almost all databases create an index for UNIQUE CONSTRAINT, otherwise it would be very hard to maintain it.

Oracle doesn't even distinguish between UNIQUE CONSTRAINT and UNIQUE INDEX: one command is just a synonym for another.

The only difference in Oracle is that a UNIQUE INDEX should have a user-supplied name, while a UNIQUE CONSTRAINT may be created with a system-generated name:

ALTER TABLE mytable MODIFY col1 UNIQUE

This will create an index called SYS_CXXXXXX.