What is the difference between the following two statements?
以下两个陈述有什么区别？
alter table [dbo].[Demo] add constraint [UC_Demo_Code] unique ( [Code] )
go
create unique nonclustered index [UK_Demo_Code] on [dbo].[Demo] ( [NB_Code] )
go
Does the constraint have an index to help it enforce uniqueness, or will a table scan be performed on every insert/update?
约束是否有索引来帮助它强制执行唯一性，还是会在每次插入/更新时执行表扫描？
3 个解决方案
#1
9
The "logical" effect is the same -- only unique values may be loaded into the table. (It's worth mentioning that if the column is nullable, only 1 row with NULL may be inserted.)
“逻辑”效果是相同的 - 只有唯一值可以加载到表中。 （值得一提的是，如果列可以为空，则只能插入一行带NULL的行。）
The physical effect is the same -- a unique index is built on the table. It could be either clustered or nonclustered.
物理效果是相同的 - 在表上构建唯一索引。它可以是群集的也可以是非群集的。
The only real difference is in the metadata, the information describing the database as stored in the system tables. The first way, it is recorded internally as an index, and the second, it is recorded as a constraint -- even though the net effects are identical. Which means that, ultimately, the only difference is the code required to create it AND to alter it in the future.
唯一真正的区别在于元数据，即描述存储在系统表中的数据库的信息。第一种方式，它在内部记录为索引，第二种方式，它被记录为约束 - 即使净效果相同。这意味着，最终，唯一的区别是创建它并在将来改变它所需的代码。
(This is true for SQL 7.0 through 2005, and I'd be very surprised if they changed it in 2008).
（对于SQL 7.0到2005，这是正确的，如果他们在2008年更改它，我会非常惊讶）。
#2
0
Excuse me for my first incorrect answer. Unique constraint is identical to unique index. Under the covers, it does create an index.
对不起我的第一个错误答案。唯一约束与唯一索引相同。在封面下，它确实创建了一个索引。
#3
0
You can Check Unique Constraints and Unique Indexes for a comparison between them.
The article concludes that there's 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. Since a unique constraint can't be disabled, having the status of a constraint doesn't give the unique constraint any additional behavior beyond a unique index. However, there are several index creation options that aren't available to the ALTER TABLE command that creates a unique constraint.
您可以检查唯一约束和唯一索引以进行比较。本文的结论是，除了唯一约束也被列为数据库中的约束对象这一事实之外，唯一约束和唯一索引之间没有实际区别。由于无法禁用唯一约束，因此具有约束状态不会为唯一索引提供唯一约束以外的任何其他行为。但是，有几个索引创建选项不可用于创建唯一约束的ALTER TABLE命令。
#1
9
The "logical" effect is the same -- only unique values may be loaded into the table. (It's worth mentioning that if the column is nullable, only 1 row with NULL may be inserted.)
“逻辑”效果是相同的 - 只有唯一值可以加载到表中。 （值得一提的是，如果列可以为空，则只能插入一行带NULL的行。）
The physical effect is the same -- a unique index is built on the table. It could be either clustered or nonclustered.
物理效果是相同的 - 在表上构建唯一索引。它可以是群集的也可以是非群集的。
The only real difference is in the metadata, the information describing the database as stored in the system tables. The first way, it is recorded internally as an index, and the second, it is recorded as a constraint -- even though the net effects are identical. Which means that, ultimately, the only difference is the code required to create it AND to alter it in the future.
唯一真正的区别在于元数据，即描述存储在系统表中的数据库的信息。第一种方式，它在内部记录为索引，第二种方式，它被记录为约束 - 即使净效果相同。这意味着，最终，唯一的区别是创建它并在将来改变它所需的代码。
(This is true for SQL 7.0 through 2005, and I'd be very surprised if they changed it in 2008).
（对于SQL 7.0到2005，这是正确的，如果他们在2008年更改它，我会非常惊讶）。
#2
0
Excuse me for my first incorrect answer. Unique constraint is identical to unique index. Under the covers, it does create an index.
对不起我的第一个错误答案。唯一约束与唯一索引相同。在封面下，它确实创建了一个索引。
#3
0
You can Check Unique Constraints and Unique Indexes for a comparison between them.
The article concludes that there's 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. Since a unique constraint can't be disabled, having the status of a constraint doesn't give the unique constraint any additional behavior beyond a unique index. However, there are several index creation options that aren't available to the ALTER TABLE command that creates a unique constraint.
您可以检查唯一约束和唯一索引以进行比较。本文的结论是，除了唯一约束也被列为数据库中的约束对象这一事实之外，唯一约束和唯一索引之间没有实际区别。由于无法禁用唯一约束，因此具有约束状态不会为唯一索引提供唯一约束以外的任何其他行为。但是，有几个索引创建选项不可用于创建唯一约束的ALTER TABLE命令。