When creating an index over a column that is going to be UNIQUE (but not the primary key of the table), SQL server let's me choose a few options:
在要创建UNIQUE（但不是表的主键）的列上创建索引时，SQL Server让我选择以下几个选项：
1) I can choose for it to be a Constraint or an Index.
I'm guessing this means that if I set it as constraint, it won't use it when querying, only when writing. However, the only efficient way I can think of for SQL Server to enforce that constraint is by actually building an index. What is the use for this option?
1）我可以选择它作为约束或索引。我猜这意味着如果我将它设置为约束，它将不会在查询时使用它，只有在写入时。但是，我能想到的SQL Server强制执行该约束的唯一有效方法是实际构建索引。这个选项有什么用？
2) Also, if I set it as "index", it let's me specify that it should ignore duplicate keys. This is the most puzzling for me...
I again guess it means the opposite of constraint. It probably means "use it when querying, but don't even check when writing".
But then why would I set it as UNIQUE?
I'm guessing there are some optimizations SQL Server can do, but i'd like to understand it better.
2）此外，如果我将其设置为“索引”，它让我指定它应该忽略重复键。这对我来说是最令人费解的......我再次猜测这意味着与约束相反。它可能意味着“在查询时使用它，但在写入时甚至不检查”。但那我为什么要把它设置为独一无二？我猜测SQL Server可以做一些优化，但我想更好地理解它。
Does anyone know what exactly SQL Server does with these options?
What's the use case for setting an index to be Unique, but ignore duplicate keys?
有谁知道SQL Server对这些选项究竟做了什么？将索引设置为Unique，但忽略重复键的用例是什么？
NOTE: This is for SQL Server 2000
注意：这适用于SQL Server 2000
EDIT: According to what you said, however... If I create a Constraint, will it be used to speed up queries that filter using the fields in the constraint?
编辑：根据你所说，然而......如果我创建一个约束，它是否会用于加速使用约束中的字段进行过滤的查询？
Thanks!
谢谢！
4 个解决方案
#1
7
A UNIQUE constraint is part of the ISO/ANSI SQL standard, whereas indexes are not because the Standard is implementation agnostic. SQL Server, in common with most SQL DBMSs, will use an index to implement a UNIQUE constraint.
UNIQUE约束是ISO / ANSI SQL标准的一部分，而索引不是因为标准是实现不可知的。与大多数SQL DBMS一样，SQL Server将使用索引来实现UNIQUE约束。
Arguably, using UNIQUE rather than index in a SQL script is slightly more portable but as always the proprietary syntax should not be ruled out if it provids opportunities for optimization etc.
可以说，在SQL脚本中使用UNIQUE而不是索引稍微容易一些，但是如果它提供了优化机会，那么不应该排除专有语法。
#2
5
SQL Server will build an index to implement UNIQUE constraints. You can see a reference to the unique index that is used to enforce unique constraints in the sys.key_constraints view (in 2005 — sorry, I don't know the 2000 equivalent). But both versions will use the index when querying.
SQL Server将构建一个索引来实现UNIQUE约束。您可以在sys.key_constraints视图中看到用于强制执行唯一约束的唯一索引的引用（在2005年 - 抱歉，我不知道2000当量）。但是这两个版本在查询时都会使用索引。
The difference is that if you create an index you have more control over how it's built. In particular, you can include additional columns that may be looked up frequently along with the key.
不同之处在于，如果您创建索引，则可以更好地控制索引的构建方式。特别是，您可以包含可以与密钥一起查找的其他列。
Both options will allow you to "ignore duplicate keys" on existing data, but both will raise an error if you attempt to insert a new value that duplicates an existing one.
这两个选项都允许您“忽略现有数据上的重复键”，但如果您尝试插入与现有数据重复的新值，则两者都会引发错误。
#3
2
There is no practical difference between a unique constraint and a unique index other than the fact that the unique constraint is also listed as a constraint object in the database.
除了唯一约束也被列为数据库中的约束对象这一事实之外，唯一约束和唯一索引之间没有实际区别。
#4
2
According to MSDN, there are no significant differences between creating a unique index thru a unique constraint, or explicitly creating using CREATE UNIQUE INDEX statement. In both the cases, data is validated for uniqueness in the same way, and also the query optimizer does not treat them any differently. As good practice, use unique constraint if data integrity is the goal, other wise use CREATE UNIQUE INDEX statement directly.
根据MSDN，通过唯一约束创建唯一索引或使用CREATE UNIQUE INDEX语句显式创建之间没有显着差异。在这两种情况下，数据都以相同的方式验证其唯一性，并且查询优化器也不会对它们进行任何不同的处理。作为良好实践，如果数据完整性是目标，则使用唯一约束，否则直接使用CREATE UNIQUE INDEX语句。
Here is an excellent article and a video explaining the difference between both the approaches.
这是一篇很好的文章和视频，解释了两种方法之间的区别。
#1
7
A UNIQUE constraint is part of the ISO/ANSI SQL standard, whereas indexes are not because the Standard is implementation agnostic. SQL Server, in common with most SQL DBMSs, will use an index to implement a UNIQUE constraint.
UNIQUE约束是ISO / ANSI SQL标准的一部分，而索引不是因为标准是实现不可知的。与大多数SQL DBMS一样，SQL Server将使用索引来实现UNIQUE约束。
Arguably, using UNIQUE rather than index in a SQL script is slightly more portable but as always the proprietary syntax should not be ruled out if it provids opportunities for optimization etc.
可以说，在SQL脚本中使用UNIQUE而不是索引稍微容易一些，但是如果它提供了优化机会，那么不应该排除专有语法。
#2
5
SQL Server will build an index to implement UNIQUE constraints. You can see a reference to the unique index that is used to enforce unique constraints in the sys.key_constraints view (in 2005 — sorry, I don't know the 2000 equivalent). But both versions will use the index when querying.
SQL Server将构建一个索引来实现UNIQUE约束。您可以在sys.key_constraints视图中看到用于强制执行唯一约束的唯一索引的引用（在2005年 - 抱歉，我不知道2000当量）。但是这两个版本在查询时都会使用索引。
The difference is that if you create an index you have more control over how it's built. In particular, you can include additional columns that may be looked up frequently along with the key.
不同之处在于，如果您创建索引，则可以更好地控制索引的构建方式。特别是，您可以包含可以与密钥一起查找的其他列。
Both options will allow you to "ignore duplicate keys" on existing data, but both will raise an error if you attempt to insert a new value that duplicates an existing one.
这两个选项都允许您“忽略现有数据上的重复键”，但如果您尝试插入与现有数据重复的新值，则两者都会引发错误。
#3
2
There is no practical difference between a unique constraint and a unique index other than the fact that the unique constraint is also listed as a constraint object in the database.
除了唯一约束也被列为数据库中的约束对象这一事实之外，唯一约束和唯一索引之间没有实际区别。
#4
2
According to MSDN, there are no significant differences between creating a unique index thru a unique constraint, or explicitly creating using CREATE UNIQUE INDEX statement. In both the cases, data is validated for uniqueness in the same way, and also the query optimizer does not treat them any differently. As good practice, use unique constraint if data integrity is the goal, other wise use CREATE UNIQUE INDEX statement directly.
根据MSDN，通过唯一约束创建唯一索引或使用CREATE UNIQUE INDEX语句显式创建之间没有显着差异。在这两种情况下，数据都以相同的方式验证其唯一性，并且查询优化器也不会对它们进行任何不同的处理。作为良好实践，如果数据完整性是目标，则使用唯一约束，否则直接使用CREATE UNIQUE INDEX语句。
Here is an excellent article and a video explaining the difference between both the approaches.
这是一篇很好的文章和视频，解释了两种方法之间的区别。