SQL SERVER:唯一约束说明

2021-09-14 00:00:00 sql-server unique-constraint

有人可以简化向列添加唯一约束的解释吗?创建键索引时，SQL SERVER 是复制行中的所有信息并将其添加到索引中，还是仅将列中的数据添加到应用了 UNIQUE CONSTRAINT 的数据中?

Could someone simplify the explanation of adding a UNIQUE CONSTRAINT to a column please. When creating the key index does SQL SERVER copy ALL of the information in the row and add it to the index or just the data in the column with the applied UNIQUE CONSTRAINT?

我希望我能正确解释.
任何帮助将不胜感激.
李.

I hope I explained that properly.
Any help will be greatly appreciated.
Lee.

编辑**

好吧，我想我明白了吗?

Ok i think i get it?

CREATE TABLE dbo.test 
    (
    Id int NOT NULL, 
    Name char(10) NOT NULL UNIQUE
    );

INSERT INTO dbo.test (id, name) VALUES (1, 'Lee')
INSERT INTO dbo.test (id, name) VALUES (2, 'Paul')
INSERT INTO dbo.test (id, name) VALUES (3, 'Adam')
INSERT INTO dbo.test (id, name) VALUES (4, 'Henry')

在聚集索引中，整个表将像这样排序

In a clustered index the whole table would be sorted like

3, Adam
4, Henry
1, Lee
2, Paul

那么，每增加一个 INSERT，服务器就必须根据 name 列对整个表重新排序?

So with each additional INSERT the server would have to re-sort the entire table based on the name column?

在非聚集索引中还有另一个表"存储排序?

In a nonclustered index there is another "table" that stores the sort?

推荐答案

UNIQUE CONSTRAINT 将与 UNIQUE INDEX 一样工作.有两种方式:

UNIQUE CONSTRAINT will work just as UNIQUE INDEX. There are 2 ways:

  • 使用聚集索引，行以与索引相同的顺序物理存储在磁盘上.(因此，只有一个聚集索引是可能的)

  • With a clustered index the rows are stored physically on the disk in the same order as the index. (hence, only one clustered index is possible)

对于非聚集索引，还有一个指向物理行的指针的第二个列表.您可以拥有多个非聚集索引，尽管每个新索引都会增加写入新记录所需的时间.

With a non clustered index there is a second list that has pointers to the physical rows. You can have many non clustered indexes, although each new index will increase the time it takes to write new records.

如果您同时拥有聚集索引和非聚集索引，那么非聚集索引将指向聚集索引列.

If you have both clustered and non clustered index, then non clustered index will point to the clustered index column.

这个'SO' 回答会帮助你理解得更清楚一些.

THIS 'SO' answer will help you understand it a bit clear.

默认情况下，唯一约束和唯一索引将创建一个非如果您没有指定任何不同的聚集索引(并且 PK 将通过如果没有冲突的聚集索引，则默认创建为 CLUSTERED存在)但您可以为任何显式指定 CLUSTERED/NONCLUSTERED其中.

By default the unique constraint and Unique index will create a non clustered index if you don't specify any different (and the PK will by default be created as CLUSTERED if no conflicting clustered index exists) but you can explicitly specify CLUSTERED/NONCLUSTERED for any of them.

相关文章