主键始终在sql server中建立索引? [英] primary key is always indexed in sql server?

问题描述

如果指定了非聚集主键约束，则可以在除主键列之外的其他列上创建聚集索引. http://msdn.microsoft.com/en-us/library/ms186342.aspx

所以上面告诉我:我可以在主键以外的列上创建聚簇索引.

So the above told me: I can create a clustered index on columns other than primary key.

我认为它也传达了一个主键应该是非集群的主键或集群的键.有可能主键没有被索引吗?

I think it also conveys that a primary key should either be a nonclustered primary key or clustered key. Is it possible a primary key is not indexed?

更多:

创建UNIQUE约束时，默认情况下会创建一个唯一的非聚集索引来强制执行UNIQUE约束.如果表上的聚簇索引尚不存在，则可以指定唯一的聚簇索引.

When you create a UNIQUE constraint, a unique nonclustered index is created to enforce a UNIQUE constraint by default. You can specify a unique clustered index if a clustered index on the table does not already exist.

这是否意味着必须创建一个聚集索引或非聚集索引的唯一约束?

Does this mean a unique constraint has to create a clustered index or a nonclustered index?

推荐答案

是否可能没有索引主键?

Is it possible a primary key is not indexed?

不，不是.

需要某种索引来管理 PRIMARY KEY ，否则将需要在每个插入内容上扫描整个表(以确保唯一性).

Some kind of an index is required to police the PRIMARY KEY, otherwise it would require scanning the whole table on each insert (to ensure uniqueness).

从文档:

数据库引擎会自动创建一个唯一索引，以强制执行 PRIMARY KEY 约束的唯一性要求.如果表上尚不存在聚集索引或未明确指定非聚集索引，则将创建唯一的聚集索引以强制执行 PRIMARY KEY 约束.

The Database Engine automatically creates a unique index to enforce the uniqueness requirement of the PRIMARY KEY constraint. If a clustered index does not already exist on the table or a nonclustered index is not explicitly specified, a unique, clustered index is created to enforce the PRIMARY KEY constraint.

---

这是否意味着必须创建一个聚集索引或非聚集索引的唯一约束?

Does this mean a unique constraint has to create a clustered index or a nonclustered index?

是的，出于同样的原因.

Yes, for the same reasons.

PRIMARY KEY 和 UNIQUE 是逻辑概念，而索引仅具有有效实现它们的副作用.因此，严格来说，更改表添加约束唯一性和 CREATE UNIQUE INDEX 的含义不同.

PRIMARY KEY and UNIQUE are logical concepts, while the index just has a side effect of implementing them efficiently. So, strictly speaking, ALTER TABLE ADD CONSTRAINT UNIQUE and CREATE UNIQUE INDEX mean different things.

如果将来某个聪明的人发明了一种使用Pauli原理或量子纠缠或在物理层上强制执行唯一性的方法，则 SQL Server 2155 可能会使用它来强制执行约束，但是仍然必须为索引创建B树.

If, in the future, some bright head invents a way to use Pauli principle or quantum entanglement or whatever to enforce uniqueness on physical level, SQL Server 2155 may employ it to enforce the constraint, but it will still have to create the B-Tree for the index.

这篇关于主键始终在sql server中建立索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！