Difference in use Index Unique and Unique Constraint in Sql Server?

Navigation
16

I would like to know what behavior / differences between a Unique Index and Unique Constraints for Sql Server ?

Many should think that this does not make a difference in the database, but it does! And not only for the DBA / Administrator, but for developers too as it can influence how a code is written.

As a reference of what may be so different and what I'm interested in knowing below is an example of how it is in an Informix database, but in SQL Server how does it work?

  

UNIQUE Index

     
  • Can be created / deleted ONLINE, with users using the table
      This can weigh heavily on the maintenance of 24x7 systems. * The validity   of oneness is done line by line. A simple example would be how   execute a UPDATE tp01 SET cod = cod + 1; in a table that field    cod is sequential and with unique index, this update would give error on the 1st line since it would already double * Like all other databases,   can be used as a reference for Foreign keys ...
    •   

Unique Constraint

     

(or even primary key constraint)

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

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

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

    
asked by anonymous 21.12.2013 / 21:37

2 answers

11

In terms of performance and assertiveness the two are virtually 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. p>

(Source)

In addition to the caveat itself, the rest of the MSDN documentation tries to differentiate between the two ways.

Despite the similarity highlighted by the documentation in MSDN, one of our sites has a response that demonstrates some small differences :

  • Violation of a unique constraint returns a 2627 error, whereas a unique index returns 2601. A unique unique constraint can not be disabled, whereas a unique index is possible.
  • unique constraints support IGNORE_DUP_KEY and FILLFACTOR (can be dependent on SQL version).
  • unique constraint can not be filtered (?)
21.12.2013 / 22:23
1

The unique key guarantees the uniqueness of information in your table, the primary key also, but the latter can be used for foreign key relationships with other tables.

Generally single keys can also have null records, so they can not be primary key. You should be aware of this.

    
13.11.2017 / 20:12
Singleton or class and static members? Multi-core CPUs - Why does not my application use all processor cores?