Under the hood a unique constraint is implemented the same way as a unique index – an index is needed to efficiently fulfill the requirement to enforce the constraint. Even if the index is created as a result of a UNIQUE constraint, the query planner can use it like any other index if it sees it as the best way to approach a given query.
So for a database that supports both features the choice of which to use will often come down to preferred style and consistency.
If you are planning to use the index as an index (i.e. your code may rely on searching/sorting/filtering on that field to be quick) I would explicitly use a unique index (and comment the source) rather than a constraint to make that clear – this way if the uniqueness requirement is changed in a later revision of the application you (or some other coder) will know to make sure a non-unique index is put in place of the unique one (just removing a unique constraint would remove the index completely). Also a specific index can be named in an index hint (i.e. WITH(INDEX(ix_index_name)), which I don’t think is the case for the index created behind the scenes for managing uniqueness as you are unlikely to know its name.
Likewise if you are only needing to enforce uniqueness as a business rule rather than the field needing to be searched or used for sorting then I’d use the constraint, again to make the intended use more obvious when someone else looks at your table definition.
Note that if you use both a unique constraint and a unique index on the same field the database will not be bright enough to see the duplication, so you will end up with two indexes which will consume extra space and slow down row inserts/updates.
In addition to the points in other answers, here are some key differences between the two.
Note: The error messages are from SQL Server 2012.
Violation of a unique constraint returns error 2627.
Msg 2627, Level 14, State 1, Line 1 Violation of UNIQUE KEY constraint 'P1U_pk'. Cannot insert duplicate key in object 'dbo.P1U'. The duplicate key value is (1). The statement has been terminated.
Violation of a unique index returns error 2601.
Msg 2601, Level 14, State 1, Line 1 Cannot insert duplicate key row in object 'dbo.P1' with unique index 'P1_u'. The duplicate key value is (1). The statement has been terminated.
A unique constraint cannot be disabled.
Msg 11415, Level 16, State 1, Line 1 Object 'P1U_pk' cannot be disabled or enabled. This action applies only to foreign key and check constraints. Msg 4916, Level 16, State 0, Line 1 Could not enable or disable the constraint. See previous errors.
But the unique index behind a primary key constraint or a unique constraint can be disabled, as can any unique index. Hat-tip Brain2000.
ALTER INDEX P1_u ON dbo.P1 DISABLE ;
Note the usual warning that disabling a clustered index makes the data inaccessible.
Unique constraints support indexing options like
IGNORE_DUP_KEY, though this hasn’t been the case for every versions of SQL Server.
Nonclustered indexes can include non-indexed columns (termed a covering index, this is a major performance enhancement). The indexes behind PRIMARY KEY and UNIQUE constraints cannot include columns. Hat-tip @ypercube.
A Unique constraint cannot be filtered.
A unique index can be filtered.
CREATE UNIQUE NONCLUSTERED INDEX Students6_DrivesLicence_u ON dbo.Students6( DriversLicenceNo ) WHERE DriversLicenceNo is not null ;
Foreign Key Constraints
A Foreign Key constraint cannot reference a filtered unique index, though it can reference a non-filtered unique index (I think this was added in SQL Server 2005).
When creating constraint, specifying a constraint name is optional (for all five types of constraints). If you don’t specify a name then MSSQL will generate one for you.
CREATE TABLE dbo.T1 ( TID int not null PRIMARY KEY ) ; GO CREATE TABLE dbo.T2 ( TID int not null CONSTRAINT T2_pk PRIMARY KEY ) ;
When creating indexes, you must specify a name.
To quote MSDN as an authoritative source:
There are no significant differences between creating a UNIQUE constraint and creating a unique index that is independent of a constraint. Data validation occurs in the same manner, and the query optimizer does not differentiate between a unique index created by a constraint or manually created. However, creating a UNIQUE constraint on the column makes the objective of the index clear… more info here
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… more info here
Other into: https://technet.microsoft.com/en-us/library/aa224827%28v=sql.80%29.aspx