問(wèn)題描述
有人可以簡(jiǎn)化向列添加唯一約束的解釋嗎?創(chuàng)建鍵索引時(shí)，SQL SERVER 是復(fù)制行中的所有信息并將其添加到索引中，還是僅將列中的數(shù)據(jù)添加到應(yīng)用了 UNIQUE CONSTRAINT 的數(shù)據(jù)中?
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')
在聚集索引中，整個(gè)表將像這樣排序
In a clustered index the whole table would be sorted like
3, Adam
4, Henry
1, Lee
2, Paul
那么，每增加一個(gè) INSERT，服務(wù)器就必須根據(jù) name 列對(duì)整個(gè)表重新排序?
So with each additional INSERT the server would have to re-sort the entire table based on the name column?
在非聚集索引中還有另一個(gè)表"存儲(chǔ)排序?
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:
使用
聚集索引，行以與索引相同的順序物理存儲(chǔ)在磁盤上.(因此，只有一個(gè)聚集索引是可能的)
With a
clustered indexthe rows are stored physically on the disk in the same order as the index. (hence, only one clustered index is possible)
對(duì)于非聚集索引，還有一個(gè)指向物理行的指針的第二個(gè)列表.您可以擁有多個(gè)非聚集索引，盡管每個(gè)新索引都會(huì)增加寫入新記錄所需的時(shí)間.
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.
如果您同時(shí)擁有聚集索引和非聚集索引，那么非聚集索引將指向聚集索引列.
If you have both clustered and non clustered index, then non clustered index will point to the clustered index column.
這個(gè)'SO' 回答會(huì)幫助你理解得更清楚一些.
THIS 'SO' answer will help you understand it a bit clear.
默認(rèn)情況下，唯一約束和唯一索引將創(chuàng)建一個(gè)非如果您沒(méi)有指定任何不同的聚集索引(并且 PK 將通過(guò)如果沒(méi)有沖突的聚集索引，則默認(rèn)創(chuàng)建為 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.
這篇關(guān)于SQL SERVER:唯一約束說(shuō)明的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！