Unique index or unique key?
Unique Key: It is a constraint which imposes limitation on database. That limitation is it will not allow duplicate values . For example if you want to select one column as primary key it should be NOT NULL & UNIQUE.
Unique Index: It is a index which improves the performance while executing queries on your data base. In unique index it also not allows duplicate values in index . ie.no two rows will have the same index key value.
This MSDN article comparing the two is what you're after. The terminology is such that "constraint" is ANSI, but in SQL Server you can't disable a Unique Constraint...
For most purposes, there's no difference - the constraint is implemented as an index under the covers. The MSDN article backs this up--the difference is in the meta-data, for things like:
- tweaking FILLFACTOR
- INCLUDE provides more efficient covering indexes (composite constraint)
- A filtered index is like a constraint over a subset of rows/ignore multiple null etc.
The unique piece is not where the difference lies. The index and key are not the same thing, and are not comparable.
A key is a data column, or several columns, that are forced to be unique with a constraint, either primary key or explicitly defined unique constraint. Whereas an index is a structure for storing data location for faster retrieval.
From the docs:
Unique Index
Creates a unique index on a table or view. A unique index is one in which no two rows are permitted to have the same index key value. A clustered index on a view must be unique
Unique key (Constraint)
You can use UNIQUE constraints to make sure that no duplicate values are entered in specific columns that do not participate in a primary key. Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key.
"Unique key" is a tautology. A Key (AKA "Candidate Key") is logical feature of the database - a constraint that enforces the uniqueness of a set of attributes in a table.
An index is a physical level feature intended to optimise performance in some way. There are many types of index.