唯一约束与唯一索引
我有兴趣了解开发人员更喜欢使用哪种技术来强制 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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
这篇比较两者的 MSDN 文章适用于 SQL Server 2000：http: //msdn.microsoft.com/en-us/library/aa224827(SQL.80).aspx
对于大多数用途来说，没有什么区别 - 约束在幕后作为索引实现。尽管可以禁用该约束，但它实际上在 SQL Server 中不起作用。
仅当您想要调整 FILLFACTOR 等内容以实现唯一约束时，这才重要。
SQL Server 2008+ 添加了
INCLUDE以提供更高效的覆盖索引。过滤索引 = 对行子集的唯一约束/忽略多个 null 等。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
INCLUDEto provide more efficient covering indexes. Filtered indexes = unique constraint over a subset of rows/ignore multiple null etc.它们没有显着差异。当您创建唯一约束时，SQL Server会自动为你创建一个唯一的索引。
使用创建索引的语法，您可以更好地控制定义唯一索引来指定聚集/非聚集、包含的列、文件组、索引过滤（SqlSvr2008）等。
在大多数情况下，约束是更可取的，因为它表达了索引的意图唯一性：它是一个约束。索引并不传达这种意图。
至于可管理性，影响很小。您可以管理索引（重建、重组），就像它是独立于约束创建的一样。唯一的区别是约束依赖于索引，因此要删除索引，您还必须删除约束。
They are not significantly different. When you create a unique constraint, SQL Server will automatically create a unique index for you.
With the syntax for creating an index, you may have better control defining a unique index to specify clustered/nonclustered, included columns, filegroup, index filtering (SqlSvr2008), etc.
A constraint is preferable in most cases because it expresses the intent of the uniqueness: it is a constraint. An index does not convey this intent.
As for manageability, the impact is minimal. You can manage the index (rebuild, reorg) as if it were created independently of the constraint. The only difference is that the constraint depends on the index, so to drop the index, you must also drop the constraint.
我的两点意见：当我想要描述业务逻辑时，我会使用约束；当我想要增强性能时，我会使用索引。它们可以在 DBMS 中以相同的方式实现这一事实并不意味着定义这些对象的原因之间的区别不重要。
My two cents: I would use a constraint when I wanted to describe business logic and an index when I wanted to enhance performance. The fact that they can be implemented the same in a DBMS does not mean that the distinction between the reasons for defining these objects is not significant.
唯一约束仅意味着唯一性，删除该约束将删除引擎创建的索引。可以更改索引以消除其唯一性，但您仍然拥有索引。我认为可以假设出于性能原因，大多数强制唯一性的列也可以很好地用作索引；因此，我主要使用唯一索引。我仅对需要唯一但性能不佳的列使用唯一约束（即需要唯一的 varchar(200)）；在这种情况下，我想明确指出，它是唯一的，但尽管有基础索引，但可能不是一个很好的搜索主意。
A unique constraint implies uniqueness only and the removal of the constraint will remove the index that the engine created. An index can be altered to remove it's uniqueness and you still have your index. I think it can be assumed that a majority of columns one would force uniqueness on would also serve well as an index for performance reasons; ergo, I use unique indexes mostly. I use unique constraints only on columns that need to be unique but serve poorly for performance (i.e. a varchar(200) that needs to be unique); in such case I want to clearly point out that it's to be unique but probably not so good an idea to search on despite the underlying index.