CODEWITHSUNDEEP
sql-serversql-server-2005
Valentin V
Valentin V

Reputation: 25739

Does creating a unique constraint on a column automatically create index?

Consider this unique constraint:

ALTER TABLE Posts
ADD CONSTRAINT UQ_Posts_Name
UNIQUE (Name);

Does it automatically create index on Name column?

Upvotes: 42

Views: 17687

Answers (4)

Svetlozar Angelov
Svetlozar Angelov

Reputation: 21660

Yes, just to add.. creating primary key automatically creates clustered index.

EDIT: I was wrong... after Lieven's comment:

When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique nonclustered index. The primary key column cannot allow NULL values.

Upvotes: 4

AutomatedTester
AutomatedTester

Reputation: 22418

You can create a separate index on it as well. this article http://msdn.microsoft.com/en-us/library/aa224827(SQL.80).aspx describes the differences very well.

Upvotes: 2

Max
Max

Reputation: 2571

Yes, absolutely. A unique constraint creates a unique index.

Upvotes: 1

n8wrl
n8wrl

Reputation: 19765

yes it does. See this

Upvotes: 36

Related Questions