唯一约束与独特指数 [英] Unique Constraint vs Unique Index

问题描述

我有兴趣学习开发人员喜欢用哪种技术在SQL Server中强制执行唯一性：UNIQUE CONSTRAINT或UNIQUE INDEX。鉴于每个人的实体执行情况几乎没有区别，您如何决定哪一个是最佳的？

评估最佳解决方案是否有其他理由？ p>

有一个或另一个数据库管理优势？

解决方案

比较两者的MSDN文章是针对SQL Server 2000的： http：/ /msdn.microsoft.com/en-us/library/aa224827(SQL.80).aspx

对于大多数用途，没有区别 - 约束作为封面下的指标实施。虽然有能力禁用约束，但在SQL Server中实际上并不起作用。

只有要调整FILLFACTOR等等，

SQL Server 2008+添加了 INCLUDE 以提供更有效的覆盖索引。过滤的索引=行的子集的唯一约束/忽略多个null等。

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?

解决方案

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.

这篇关于唯一约束与独特指数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！