Saturday, January 22, 2011

UNIQUE constraints Vs UNIQUE index

The differences between the two methods are very subtle. UNIQUE constraints are part of the ANSI SQL definition and defining UNIQUE constraints is part of defining a database's logical design. In addition, UNIQUE constraints can be created as part of a table's definition and, as a by-product, SQL Server will automatically create a unique index under the hood when the constraint is created. In contrast, defining unique indexes are part of designing the physical data model and they are not ANSI standard.

From a performance standpoint, UNIQUE constraints and unique indexes are effectively the same to the query optimizer and you will not see any performance benefit to using one vs the other.

No comments:

Post a Comment