一、索引塊與數(shù)據(jù)塊的區(qū)別
大家都知道，索引可以提高檢索效率，因?yàn)樗亩鏄浣Y(jié)構(gòu)以及占用空間小，所以訪問速度塊。讓我們來算一道數(shù)學(xué)題：如果表中的一條記錄在磁盤上占用1000字節(jié)的話，我們對其中10字節(jié)的一個(gè)字段建立索引，那么該記錄對應(yīng)的索引塊的大小只有10字節(jié)。我們知道，SQL Server的最小空間分配單元是“頁（Page）”，一個(gè)頁在磁盤上占用8K空間，那么這一個(gè)頁可以存儲上述記錄8條，但可以存儲索引800條?，F(xiàn)在我們要從一個(gè)有8000條記錄的表中檢索符合某個(gè)條件的記錄，如果沒有索引的話，我們可能需要遍歷8000條×1000字節(jié)/8K字節(jié)=1000個(gè)頁面才能夠找到結(jié)果。如果在檢索字段上有上述索引的話，那么我們可以在8000條×10字節(jié)/8K字節(jié)=10個(gè)頁面中就檢索到滿足條件的索引塊，然后根據(jù)索引塊上的指針逐一找到結(jié)果數(shù)據(jù)塊，這樣IO訪問量要少的多。
二、索引優(yōu)化技術(shù)
是不是有索引就一定檢索的快呢？答案是否。有些時(shí)候用索引還不如不用索引快。比如說我們要檢索上述表中的所有記錄，如果不用索引，需要訪問8000條×1000字節(jié)/8K字節(jié)=1000個(gè)頁面，如果使用索引的話，首先檢索索引，訪問8000條×10字節(jié)/8K字節(jié)=10個(gè)頁面得到索引檢索結(jié)果，再根據(jù)索引檢索結(jié)果去對應(yīng)數(shù)據(jù)頁面，由于是檢索所有數(shù)據(jù)，所以需要再訪問8000條×1000字節(jié)/8K字節(jié)=1000個(gè)頁面將全部數(shù)據(jù)讀取出來，一共訪問了1010個(gè)頁面，這顯然不如不用索引快。
三、聚簇索引與非聚簇索引的本質(zhì)區(qū)別
現(xiàn)在可以討論聚簇索引與非聚簇索引的本質(zhì)區(qū)別了。正如本文最前面的兩個(gè)圖所示，聚簇索引的葉節(jié)點(diǎn)就是數(shù)據(jù)節(jié)點(diǎn)，而非聚簇索引的頁節(jié)點(diǎn)仍然是索引檢點(diǎn)，并保留一個(gè)鏈接指向?qū)?yīng)數(shù)據(jù)塊。
還是通過一道數(shù)學(xué)題來看看它們的區(qū)別吧：假設(shè)有一8000條記錄的表，表中每條記錄在磁盤上占用1000字節(jié)，如果在一個(gè)10字節(jié)長的字段上建立非聚簇索引主鍵，需要二叉樹節(jié)點(diǎn)16000個(gè)（這16000個(gè)節(jié)點(diǎn)中有8000個(gè)葉節(jié)點(diǎn)，每個(gè)頁節(jié)點(diǎn)都指向一個(gè)數(shù)據(jù)記錄），這樣數(shù)據(jù)將占用8000條×1000字節(jié)/8K字節(jié)=1000個(gè)頁面；索引將占用16000個(gè)節(jié)點(diǎn)×10字節(jié)/8K字節(jié)=20個(gè)頁面，共計(jì)1020個(gè)頁面。
同樣一張表，如果我們在對應(yīng)字段上建立聚簇索引主鍵，由于聚簇索引的頁節(jié)點(diǎn)就是數(shù)據(jù)節(jié)點(diǎn)，所以索引節(jié)點(diǎn)僅有8000個(gè)，占用10個(gè)頁面，數(shù)據(jù)仍然占有1000個(gè)頁面。
下面我們看看在執(zhí)行插入操作時(shí)，非聚簇索引的主鍵為什么比聚簇索引主鍵要快。主鍵約束要求主鍵不能出現(xiàn)重復(fù)，那么SQL Server是怎么知道不出現(xiàn)重復(fù)的呢？唯一的方法就是檢索。對于非聚簇索引，只需要檢索20個(gè)頁面中的16000個(gè)節(jié)點(diǎn)就知道是否有重復(fù)，因?yàn)樗兄麈I鍵值在這16000個(gè)索引節(jié)點(diǎn)中都包含了。但對于聚簇索引，索引節(jié)點(diǎn)僅僅包含了8000個(gè)中間節(jié)點(diǎn)，至于會(huì)不會(huì)出現(xiàn)重復(fù)必須檢索另外1000個(gè)頁數(shù)據(jù)節(jié)點(diǎn)才知道，那么相當(dāng)于檢索10+1000=1010個(gè)頁面才知道是否有重復(fù)。所以聚簇索引主鍵的插入速度要比非聚簇索引主鍵的插入速度慢很多。
讓我們再來看看數(shù)據(jù)檢索的效率，如果對上述兩表進(jìn)行檢索，在使用索引的情況下（有些時(shí)候SQL Server執(zhí)行計(jì)劃會(huì)選擇不使用索引，不過我們這里姑且假設(shè)一定使用索引），對于聚簇索引檢索，我們可能會(huì)訪問10個(gè)索引頁面外加1000個(gè)數(shù)據(jù)頁面得到結(jié)果（實(shí)際情況要比這個(gè)好），而對于非聚簇索引，系統(tǒng)會(huì)從20個(gè)頁面中找到符合條件的節(jié)點(diǎn)，再映射到1000個(gè)數(shù)據(jù)頁面上（這也是最糟糕的情況），比較一下，一個(gè)訪問了1010個(gè)頁面而另一個(gè)訪問了1020個(gè)頁面，可見檢索效率差異并不是很大。所以不管非聚簇索引也好還是聚簇索引也好，都適合排序，聚簇索引僅僅比非聚簇索引快一點(diǎn)。
1.建立數(shù)據(jù)表：
create table choice
(
s_no char(6),
course_no char(5),
score numeric(6,1),
constraint cho_pri primary key(s_no,course_no)
)
2.添加新的字段
alter table choice
add choice_socre numeric(6,1)
--注意，這個(gè)字段會(huì)自動(dòng)填充NULL值，所以，一定不能在新建的這個(gè)字段中使用not null 約束。因此，也不能添加primary key約束。當(dāng)然，可以添加unique約束（unique允許有null值）。
alter table choice
add palgerrard char(6) check(palgerrard<>'pal') --字段和約束同時(shí)添加
alter table choice
add shuang char(5) unique --字段和約束同時(shí)添加
3.添加約束條件
alter table choice
add constraint sco_che check(score>=0 and score<=100)
alter table choice
add constraint pal_uni unique (score)
--注意：這里的unique約束，要用（ ）將約束的字段括起來， check 約束就不需要了。而且，如果table choice中已有數(shù)據(jù)內(nèi)容。那么score中若有相同的值，這個(gè)約束就會(huì)添加失敗。若score中沒有相同的內(nèi)容，這個(gè)約束可以成功添加。同理，check約束也會(huì)根據(jù)已有的數(shù)據(jù)項(xiàng)，判斷是否可以添加。
4.字段的刪除，約束的刪除
要?jiǎng)h除一個(gè)字段，如果該字段有約束。要先刪除約束，再刪除字段。
alter table choice
drop column course_no --字段的刪除要有column關(guān)鍵字,否則course_no被認(rèn)為是約束
alter table choice
drop constraint sco_che 或者 drop sco_che
5.關(guān)鍵字約束
關(guān)鍵字約束可以被刪除，若刪除了一個(gè)表中的關(guān)鍵字，該表變?yōu)?#8220;只讀”表。關(guān)鍵字被刪除了，不能通過add添加新字段并關(guān)鍵字約束。例如add pal char(6) primary key ，這句錯(cuò)了，add pal char（6）會(huì)為pal字段自動(dòng)設(shè)置null約束。注意：通過add添加新字段自動(dòng)填充NULL值 ，關(guān)鍵字只可以添加在not null 約束的字段上！
create table gerrard
(
s_no char(6) not null,
course_no char(5), //系統(tǒng)默認(rèn)null
score numeric(6,1),
)
alter table gerrard
add constraint cc_pri primary key (s_no)
我們可以刪除表的關(guān)鍵字，add一個(gè)新字段（null），修改為not null 約束，然后添加pimary key 約束
create table shuang
(
s_no char(6) ,
course_no char(5),
score numeric(6,1),
)
alter table shuang
add pri char(4)
alter table shuang
alter column
pri varchar(3) not null --這里修改了not null 約束 ，關(guān)鍵！
alter table shuang
add constraint ss_pri primary key (pri)
6.alter的限制
* 不能改變列名
* 不能將含有空值的列得定義修改為not null
* 若列中已有數(shù)據(jù)，則不能減少該列的寬度，也不能改變其數(shù)據(jù)類型
* 只能修改null/not 約束，其它類型的約束在修改之前必須先將約束刪除，然后再重新添加修改過的約束定義。
7.聚集索引，關(guān)鍵字
關(guān)鍵字會(huì)自動(dòng)建立聚集的唯一索引，unique約束會(huì)自動(dòng)建立非聚集的唯一索引
下面的例子，綜合運(yùn)用了上面的很多知識
drop table number
create table number
(
turn int ,
next int ,
sign int
) --在查詢分析器中，此表只“可讀”
create unique index pri_unique on number(turn asc) --建立了唯一索引，表可寫了，不過這里的asc升序排列對表中數(shù)據(jù)無影響啊？？？
/*alter table number
alter column turn int not null*/ /* 前面有了索引，不能修改列turn */
alter table number
alter column sign int not null --將在sign中添加關(guān)鍵字約束，修改sign為 not null 約束
alter table number
add constraint trun_pri primary key (sign) --設(shè)置sign為關(guān)鍵字，sign自動(dòng)獲得聚集索引
alter table number
drop trun_pri --現(xiàn)在想在turn設(shè)置聚集索引，而一個(gè)表中只能有一個(gè)聚集索引，所以要?jiǎng)h除關(guān)鍵字
create clustered index xx_index on number (turn desc) --clustered 為聚集索引，降序排列
alter table number
add constraint trun_pri primary key (sign) --重新設(shè)置sign為關(guān)鍵字
