Unique index or unique key?

Question

What is the diffrence between a unique index and a unique key?

Festoon

Answer 1

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.

Saundrasaunter

May be this answer makes some sense in general, but this question raises from MS SQL Management Studio interface where you can literally chose between 3 options - Index (which you can make Unique or not), Unique Key & Columnstore index. So, I still not sure what would be practical difference between choosing Unique Key VS an (Unique) Index. – Cavanagh

Answer 2

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.

Archaeozoic

"the constraint is implemented as an index under the covers" - absolutely right, and it would be impossible to enforce efficiently without an index in any case. – Stowell

@ReversedEngineer I suppose how SQL Server chooses to implement a unique constraint is entirely an internal implementation detail. It could choose to do a table scan even time. Or might decide that since you already have an index covering what it needs, it will just do that. And if you possibly delete that other index, it might then create its own. All you can really guarantee about a unique constraint is that it is a constraint. Whereas if you create a unique index, you're definitely getting an index. – Metallurgy

Answer 3

Here are few key differences:

Purpose:

• Unique Key: Ensures integrity of data at table level, so that no duplicates can be entered in the table. Is not used for query planning, does not contribute to query speed. (It's different purpose than Primary Key, primary key uniquely identifies each record for data operations such as update / delete etc. In complex tables, a unique key can be combinations of several columns and it will be inefficient to use unique key for identifying records for transactions. Hence primary key is quick way of identifying a particular record in the table, while unique key guarantees that no two records have same key attributes.)
• Unique Index: Ensures uniqueness of data at index level, cannot guarantee uniqueness at the table level e.g. in case of filtered index. Is used for query planning and fetching data and thus speeds up queries depending on columns used / queried.

Filter Option:

• Unique Key: Filter option is not available
• Unique Index: Filter option is available

Storage Option:

• Unique Key: Filegroup only
• Unique Index: Filegroup or partition

Icon:

• Unique Key: Icon is vertical key [ ]
• Unique Index: Icon is b-tree [ ]

Kimbell

Answer 4

"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.

Dedie

Answer 5

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.

Illumination

Answer 6

Both the key (aka keyword) and index are identifiers of a table row.
Though index is parallel identification structure, containing a pointer to the identified row, while keys are in situ field members.

The key, as identifier, implies uniqueness (constraint) and NOT NULL (constraint). There is no sense in NULL as identifier (as null cannot identify anything) as well nonunique identifying value.
Non-clustered index can contain real data, not serving as identifier to real data, and so be non-unique [1]

It is unfortunate practice that the key or index (identifier) is called by constraint (rule or restriction) what most previous answers here followed.

Keys are used in context of:

• alternate aka secondary aka candidate keys, can be multiple
• composite key (a few fields combined)
• primary key (superkey), natural or surrogate key, only one, really used for referential integrity
• foreign key

Foreign key is the key in another table (where it is primary key) and even not a key to which they frequently refer. Such use is explained by confusing shortcutting of "foreign key constraint" term to just "foreign key".

Primary key constraint really implies NOT NULL and UNIQUE constraints + that referenced column (or combined columns) is identifier and also unfortunately substituted by "primary key" or "primary key constraint" while it is both which cannot be called either by only (primary key) constraint or by only (primary) key.

---

Update:
My related question:
[1]
UNIQUE argument for INDEX creation - what's for?

Brinkman

Answer 7

The functionalities are more or less same, it’s dependent on your use case.

Suppose you want to permit duplicate rows based on CUSTOMER_ID and TEAM_NAME.

In that case you can use both:

• UNIQUE INDEX idx_customer_id_name (CUSTOMER_ID,TEAM_NAME)
• UNIQUE KEY unique_key_customer_id_name (CUSTOMER_ID,TEAM_NAME)

But you should consider how often you fetch records based on CUSTOMER_ID AND TEAM_NAME. If it is more, then you should use unique index as it would help in faster retrieval of records otherwise you should go with unique key as it would prevent overheard of fetching based on index.

Chronon