INDEX创建的UNIQUE参数 - 有什么用？ [英] UNIQUE argument for INDEX creation - what's for?

问题描述

为什么INDEX创建语句有UNIQUE参数？

Why does INDEX creation statement have UNIQUE argument?

据我所知，非聚集索引包含一个书签，一个指向行的指针，它应该是唯一的为了区分非独特的行，
b $ b 所以确保非聚集索引是唯一的？

正确吗？

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

• 视图上的聚集索引必须是唯一的[1]

由于聚集索引的底部或叶子级别包含表的实际数据行[1]，我是否正确理解与聚集索引上的UNIUE相同的效果可以通过对表[2]的（可能是全部或部分）列的唯一约束来实现？

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]?

那么，什么为索引带来了UNIQUE参数？ / strong>

除了混淆基本概念定义[3]

Then, what does bring UNIQUE argument for index?
except confusion to basic concepts definitions [3]

更新：

这又是一个陷阱 - 根据未定义的术语解释已经多次解释的内容，将所有解释转换为永无止境的猜谜游戏。

请参阅我的子问题[4]这里同样问题的措辞。

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：

问题是模糊不清，缺乏定义或不当使用条款不当上下文。如果index被定义为服务于（查找和）识别/指向实际数据的结构，则非唯一或NULL索引没有任何意义。再见

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

引用：

[1]

CREATE INDEX（Transact -SQL）

http://msdn.microsoft。 com / zh-CN / library / ms188783.aspx

[2]

CREATE TABLE（Transact-SQL）
http://msdn.microsoft.com/en-us/library/ms174979 .aspx

[3]

唯一索引或唯一密钥？

唯一索引或唯一密钥？

[4]

什么是索引，非聚集索引可以是非唯一的？

什么是索引，非聚集索引可以是非唯一的？

推荐答案

UNIQUE in dex子句实际上只是SQL Server和其他一些DBMS中语法的怪癖。在标准SQL中，唯一性约束是通过使用PRIMARY KEY和UNIQUE CONSTRAINT语法实现的，而不是通过索引实现的（标准SQL中没有索引）。

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).

机制SQL Server在内部使用实现唯一性约束称为唯一索引。每当您创建PRIMARY KEY或UNIQUE约束时，都会自动为您创建唯一索引。出于SQL Server开发团队最熟悉的原因，他们决定将UNIQUE关键字作为CREATE INDEX语法的一部分公开，即使约束语法执行相同的工作。

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.

为了清晰和标准支持，我建议您尽可能避免显式创建UNIQUE索引。改为使用PRIMARY KEY或UNQIUE约束语法。

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.

这篇关于INDEX创建的UNIQUE参数 - 有什么用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！