唯一约束与唯一索引 [英] Unique Constraint vs Unique Index

问题描述

我有兴趣了解开发人员更喜欢使用哪种技术来强制 SQL Server 中的唯一性:UNIQUE CONSTRAINT 或 UNIQUE INDEX.鉴于每个的物理实现几乎没有区别，您如何决定哪个最好?

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?

推荐答案

这篇比较两者的 MSDN 文章适用于 SQL Server 2000:http://msdn.microsoft.com/en-us/library/aa224827(SQL.80).aspx

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

对于大多数用途，没有区别 - 约束是作为索引实现的.尽管可以禁用约束，但它实际上在 SQL Server 中不起作用.

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.

仅当您想调整 FILLFACTOR 等内容以实现独特约束的方式时才重要.

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+ 添加了 INCLUDE 以提供更有效的覆盖索引.过滤索引 = 对行子集的唯一约束/忽略多个空值等

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屋！