创建 INDEX 的唯一参数 - 用途是什么？
为什么 INDEX 创建语句有 UNIQUE 参数？
据我了解，非聚集索引包含一个书签，一个指向行的指针，它应该是唯一的，以区分甚至非唯一的行，
确保非聚集索引是唯一的？
正确吗？
那么，我是否理解非唯一索引只能在聚集表上？由于
- “视图上的聚集索引必须是唯一的”[1]
由于“聚集索引的底部或叶子级别包含表的实际数据行”[1]，我是否正确理解与聚集索引上的 UNIUE 可以通过对表的（可能是全部或部分）列的唯一约束来实现 [2]？
那么，什么给索引带来了UNIQUE参数呢？
除了对基本概念定义的混淆之外 [3]
更新：
这又是同样的陷阱 - 根据未定义的术语解释已经解释过很多次的东西，将所有解释转换为永无休止的猜测游戏。
请参阅我的子问题 [4]，它实际上是对同一问题的重新措辞。
更新2：
问题在于含糊不清、缺乏定义或在不适当的上下文中使用不当的术语。如果索引被定义为用于（查找和）识别/指向真实数据的结构，则非唯一或 NULL 索引没有任何意义。再见
引用：
[1]
创建索引 (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms188783。 aspx
[2]
创建表 (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms174979。 aspx
[3]
唯一索引还是唯一键？
唯一索引还是唯一键？
[4]
什么是索引？非聚集索引可以不唯一吗？
什么是索引以及非聚集索引可以是非唯一？
Why does INDEX creation statement have UNIQUE argument?
As I understand, the non-clustered index contains a bookmark, a pointer to a row, which should be unique to distinguish even non-unique rows,
so insuring non-clustered index to be unique ?
Correct?
So, do I understand that no-unique index can be only on clustered table? since
- "A clustered index on a view must be unique" [1]
Since "The bottom, or leaf, level of the clustered index contains the actual data rows of the table" [1], do I understand correctly that the same effect as UNIUE on clustered index can be achieved by unique constraint on (possibly all or part of) columns of a table [2]?
Then, what does bring UNIQUE argument for index?
except confusion to basic concepts definitions [3]
Update:
This is again the same pitfall - explaining something already explained many times based on undefined terms converting all explanation to never-ending guessing game.
Please see my subquestion [4] which is really re-wording of this same question here.
Update2:
The problem is in ambiguous, lacking definitions or improper use of terms in improper contexts. If index is defined as structure serving to (find and) identify/point to real data, then non-unique or NULL indexes do not make any sense. Bye
Cited:
[1]
CREATE INDEX (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms188783.aspx
[2]
CREATE TABLE (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms174979.aspx
[3]
Unique index or unique key?
Unique index or unique key?
[4]
what is index and can non-clustered index be non-unique?
what is index and can non-clustered index be non-unique?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
虽然非唯一索引足以区分行（如您所说），但
UNIQUE索引充当约束：它将防止重复项输入数据库 - 其中“重复项”是行索引列中包含相同的数据。示例：
假设默认情况下，登录名是根据名字 + 姓氏的第一个字母生成的。
当我们尝试将 Joe Sciavillo 添加到数据库时会发生什么？通常，系统会很乐意生成登录名
joes并插入(Joe,Sciavillo,joes)。现在我们有两个用户具有相同的用户名 - 可能是一件坏事。现在，假设我们在
Login列上有一个UNIQUE索引 - 数据库将检查是否已存在具有相同数据的其他行，然后才允许插入新行。换句话说，插入另一个 joes 的尝试将被拒绝，因为该数据在该行中不再是唯一的。当然，您可以在多个列上拥有唯一索引，在这种情况下，数据的组合必须是唯一的（例如
Firstname,Lastname上的唯一索引将很乐意接受包含(Joe,Badzhanov)的行，因为该组合尚未在表中，但会拒绝包含(Joe,Smith)的第二行）While a non-unique index is sufficient to distinguish between rows (as you said), the
UNIQUEindex serves as a constraint: it will prevent duplicates from being entered into the database - where "duplicates" are rows containing the same data in the indexed columns.Example:
Let's assume that login names are by default generated from first name + first letter of last name.
What happens when we try to add Joe Sciavillo to the database? Normally, the system would happily generate loginname
joesand insert(Joe,Sciavillo,joes). Now we'd have two users with the same username - probably a Bad Thing.Now let's say we have a
UNIQUEindex onLogincolumn - the database will check that no other row with the same data already exists, before it allows inserting the new row. In other words, the attempt to insert anotherjoeswill be rejected, because that data wouldn't be unique in that row any more.Of course, you could have unique indexes on multiple columns, in which case the combination of data would have to be unique (e.g. a unique index on
Firstname,Lastnamewill happily accept a row with(Joe,Badzhanov), as the combination is not in the table yet, but would reject a second row with(Joe,Smith))UNIQUE 索引子句实际上只是 SQL Server 和其他一些 DBMS 中的语法怪癖。在标准 SQL 中，唯一性约束是通过使用 PRIMARY KEY 和 UNIQUE CONSTRAINT 语法来实现的，而不是通过索引（标准 SQL 中没有索引）。
SQL Server 在内部使用的实现唯一性约束的机制称为唯一索引。每当您创建 PRIMARY KEY 或 UNIQUE 约束时，都会自动为您创建唯一索引。由于 SQL Server 开发团队最了解的原因，他们决定将 UNIQUE 关键字公开为 CREATE INDEX 语法的一部分，尽管约束语法执行相同的工作。
为了清晰和标准支持，我建议您尽可能避免显式创建 UNIQUE 索引。请改用 PRIMARY KEY 或 UNQIUE 约束语法。
The UNIQUE index clause is really just a quirk of syntax in SQL Server and some other DBMSs. In Standard SQL, uniqueness constraints are implemented through the use of the PRIMARY KEY and UNIQUE CONSTRAINT syntax, not through indexes (there are no indexes in standard SQL).
The mechanism SQL Server uses internally to implement uniqueness constraints is called a unique index. A unique index gets created automatically for you whenever you create a PRIMARY KEY or UNIQUE constraint. For reasons best known to the SQL Server development team they decided to expose the UNIQUE keyword as part of the CREATE INDEX syntax, even though the constraint syntax does the same job.
In the interests of clarity and standards support I would recommend you avoid creating UNIQUE indexes explicitly wherever possible. Use the PRIMARY KEY or UNQIUE constraint syntax instead.
UNIQUE 子句指定列中的值在整个表中必须是唯一的，本质上是添加唯一约束。表上的聚集索引指定表中行的顺序与索引相同。非聚集索引不会改变物理顺序，这就是为什么可以有多个非聚集索引但只有一个聚集索引。表上可以有唯一或非唯一的聚集索引和非聚集索引。
The UNIQUE clause specifies that the values in the column(s) must be unique across the table, essentially adding a unique constraint. A clustered index on a table specifies that the ordering of the rows in the table will be the same as the index. A non-clustered index does not change the physical ordering, which is why it is OK to have multiple non-clustered but only one clustered index. You can have unique or non-unique clustered and non-clustered indexes on a table.
我认为根本的问题是：唯一索引和非唯一索引有什么区别？
答案是，唯一索引中的条目只能指向一行，而非唯一索引中的条目可以指向许多行。
例如，考虑一个订单项表：
- 在 ORDER_NO 和 LINE_NO 上具有唯一索引，在 PRODUCT_NO 上具有非唯一索引。
对于 ORDER_NO 和 LINE_NO 的单个组合，表中只能有一个条目，而对于 PRODUCT_NO 的单个值，表中可以有多个条目（因为索引中该值会有多个条目）。
I think the underlying question is: what is the difference between unique and non-unique indexes?
The answer is that entries in unique indexes can each only point to a single row, while entries in non-unique indexes can point to many rows.
For example, consider an order item table:
- with a unique index on ORDER_NO and LINE_NO, and a non-unique index on PRODUCT_NO.
For a single combination of ORDER_NO and LINE_NO there can only be one entry in the table, while for a single value of PRODUCT_NO there can be many entries in the table (because there will be many entries for that value in the index).