CODEWITHSUNDEEP
sql-serversql-server-2008
John Jai
John Jai

Reputation: 3723

Automatic index creation for Unique constraint

The database used is Microsoft SQL Server 2008.

When I create a UNIQUE constraint on a column, will automatic indexing will be created or I create an Index for the column manually?

From below para from http://msdn.microsoft.com/en-us/library/ms175132(v=sql.105).aspx

When you create a UNIQUE constraint, a unique nonclustered index is created to enforce a UNIQUE constraint by default

So it means some index (not sure what's nonclustered) at least is created. Please correct my understanding.

Upvotes: 0

Views: 77

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

Yes the UNIQUE constraint is a logical concept that for SQL Server (and most DBMS systems) also involves a PHYSICAL index creation to enforce it.

It otherwise becomes counterproductive to maintain, since every single insert requires a full table scan just to make sure it doesn't violate the constraint.

Upvotes: 1

Related Questions