Why Microsoft has put 2 different options for creating Unique Indexes on a table in SQL Server 2008?
Manage Indexes/Keys :
1 ) type = Unique Key ==> IsUnique = Yes (IsUnique is disabled) 2 ) type = Index ==> IsUnique = Yes
Thank you
views:
37answers:
2
+2
Q:
Why Microsoft has put 2 different options for creating Unique Indexes on a table in SQL Server 2005?
+5
A:
They map to two different SQL commands:
ALTER TABLE … ADD CONSTRAINT … UNIQUE
and
CREATE UNIQUE INDEX … ON …
CONSTRAINT UNIQUE is a logical concept, while UNIQUE INDEX is its physical implementation.
In SQL Server, CONSTRAINT UNIQUE is always backed up by a unique index which is implicitly created with the same name as the constraint, so these commands are effectively the same.
The only difference from user's point of view is that the constraint can be implicitly named, but you should always provide an explicit name for an index.
Quassnoi
2010-05-11 11:28:33
Okay, I'll bite... Is there a difference in the physical implementation? AFAIK, adding a unique constraint or a unique index both achieve the same thing... or don't they?
Lieven
2010-05-11 11:35:12
@Lieven : +1 for good question.
odiseh
2010-05-11 11:37:24
@Lieven: as for now, there is no difference, but in future releases there can be. `UNIQUE CONSTRAINT` is a logical concept (make sure that the column's value is unique), `CREATE UNIQUE INDEX` is a physical concept (create a `B-Tree` that does not allow duplicates). If some wise guy will invent a more efficient way to ensure uniqueness, `UNIQUE CONSTRAINT` can use this efficient way, while `CREATE UNIQUE INDEX` cannot.
Quassnoi
2010-05-11 11:45:36
@Quassnoi: I'm stumped - thanks for sharing that!
marc_s
2010-05-11 12:12:09
A:
This is what Google finds (for SQL Server 2000 though):
The short answer is that a unique index is just an index, whereas a unique constraint is a unique index that's listed as a constraint object in the database. In the sysobjects table, the unique constraint will have an xtype value of "UQ." But does a unique constraint have any additional behavior that a unique index doesn't have—or vice versa? The answer to that question, it turns out, takes a big of digging.
...
scherand
2010-05-11 11:36:17