Unique Constraint vs Unique Index

Question

I’m interested in learning which technique developers prefer to use to enforce uniqueness in SQL Server: UNIQUE CONSTRAINT or UNIQUE INDEX. Given that there is little difference in the physical implementation of each, how do you decide which is best?

Are there reasons other than performance to evaluate the best solution?

Are there database management advantages to one or the other?

Jarvisjary

Answer 1

This MSDN article comparing the two is for SQL Server 2000: http://msdn.microsoft.com/en-us/library/aa224827(SQL.80).aspx

For most purposes, there's no difference - the constraint is implemented as an index under the covers. And though there's the ability to disable the constraint, it doesn't actually work in SQL Server.

It only matters if you want to tweak things like FILLFACTOR, etc for which way you want to implement the unique constraint.

SQL Server 2008+ added INCLUDE to provide more efficient covering indexes. Filtered indexes = unique constraint over a subset of rows/ignore multiple null etc.

Londoner

The article is good. It's interesting that it concludes "that there's no practical difference" other than meta-data. – Jarvisjary

tut, tut quoting MSDN for SQL Server 2000 – Rumph

@gbn: True, but has anything changed? – Londoner

let's see. INCLUDE = more efficient covering indexes. Filtered indexes = unique constraint over a subset of rows/ignore multiple null etc. These are the 2 headline differences. – Rumph

Answer 2

They are not significantly different. When you create a unique constraint, SQL Server will automatically create a unique index for you.

With the syntax for creating an index, you may have better control defining a unique index to specify clustered/nonclustered, included columns, filegroup, index filtering (SqlSvr2008), etc.

A constraint is preferable in most cases because it expresses the intent of the uniqueness: it is a constraint. An index does not convey this intent.

As for manageability, the impact is minimal. You can manage the index (rebuild, reorg) as if it were created independently of the constraint. The only difference is that the constraint depends on the index, so to drop the index, you must also drop the constraint.

Adaurd

+1 I'd also say consistency, as well as you'll often end up changing a constraint to an index anyway to add an INCLUDE – Rumph

Answer 3

My two cents: I would use a constraint when I wanted to describe business logic and an index when I wanted to enhance performance. The fact that they can be implemented the same in a DBMS does not mean that the distinction between the reasons for defining these objects is not significant.

Kauffman

Answer 4

A unique constraint implies uniqueness only and the removal of the constraint will remove the index that the engine created. An index can be altered to remove it's uniqueness and you still have your index. I think it can be assumed that a majority of columns one would force uniqueness on would also serve well as an index for performance reasons; ergo, I use unique indexes mostly. I use unique constraints only on columns that need to be unique but serve poorly for performance (i.e. a varchar(200) that needs to be unique); in such case I want to clearly point out that it's to be unique but probably not so good an idea to search on despite the underlying index.

Crenulation