Difference in Indice Unique and Unique Constraint usage in Sql Server?

Question

I would like to know the behavior/differences between a Indice Unique and Unique Constraints to the Sql Server ?

Many may think that it makes no difference in the database, but it does! And not only for the DBA/Administrator, but also for the developers because it can influence how a code is written.

---

As a reference of what may be so different and what I’m interested in knowing follows below an example of how it is in a bank Informix , but in SQL Server how it works?

Indice UNIQUE

• Can be created/deleted ONLINE, with users using the table
  This can weigh heavily on the maintenance of 24x7 systems. * Validation oneness is done line by line. A simple example, would be like execute a UPDATE tp01 SET cod = cod + 1; in a table that the field Cod is sequential and with Intel unico, this update would error in the first line as it would already duplicate * Like all other databases, no can be used as a reference for Foreign Keys...

Unique Constraint

(or even Primary key Constraint)

• Cannot be deleted/created with users accessing the table, even read-only. In other words you need exclusive access in the table to perform a maintenance. This is bad in systems 24x7. * Validation is done at the end of the statement In the case of UPDATE tp01 SET cod = cod + 1; , would work smoothly because it will validate the uniqueness only when finalizing the update of all lines involved. * It is possible to postpone the validation of the Constraint only at the end of the transaction. That is, at the time of the commit. This is possible when using the command set constraints [all|<constraint>] deffered; before the updates.
  So in the update example above, it will only validate uniqueness when the commit is sent to the bank. Note: This resource applies to all types of constraints (PK, FK, UK)

Source: Difference in Use Index Unique and Unique Constraint in Informix?

Remember, the description above is how IBM Informix works. I would like to know how it is in other banks and understand what additional limitations or resources we can provide for developers.

Answer 1

In terms of performance and assertiveness the two are practically equivalent:

A Unique index guarantees that the index key contains no Duplicate values and therefore Every Row in the table is in some way Unique. 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.

(Source)

Beyond the caveat itself, the rest of the MSDN documentation tries little to differentiate the two ways.

---

Despite the similarity highlighted by the documentation in MSDN, one of our websites brothers has an answer that shows some small differences:

• Violation of a Unique Constraint returns an error 2627, while a Unique index returns 2601.
• One Unique Constraint cannot be disabled while with a Unique index it is possible.
• Unique constraints support IGNORE_DUP_KEY and FILLFACTOR (may be dependent on SQL version).
• Unique Constraint cannot be filtered (?)

Answer 2

The key Unique ensures the uniqueness of information in your table, the key Primary can also be used for Foreign key relationships with other tables.

Usually single keys can also have null records, so they cannot be primary key. One should be aware of this.