Why did Microsoft provide two different options for creating unique indexes on a table in SQL Server 2005?
They map to two different commands SQL
:
ALTER TABLE … ADD CONSTRAINT … UNIQUE
and
CREATE UNIQUE INDEX … ON …
CONSTRAINT UNIQUE
is a logical concept, and UNIQUE INDEX
is its physical implementation.
B SQL Server
, is CONSTRAINT UNIQUE
always backed by a unique index that is implicitly created with the same name as the constraint, so these commands are effectively the same.
The only difference from the custom point of view is that the constraint can be implicit, but you must always provide an explicit name for the index.
a source to share
This is what Google finds (for SQL Server 2000):
The short answer is that a unique index is just an index, whereas a unique constraint is a unique index that is specified as a constraint object in the database. In sisoobjects of a table, the only constraint will be the xtype value for "UQ". But does the unique constraint have any additional behavior that the unique index is not, or vice versa? The answer to this question, it turns out, takes a lot of digging.
...
a source to share