索引學習筆記

叢集索引和非叢集索引最大的差異是,在設定叢集索引時,資料本身也會按照該索引的順序來存放.
至於非叢集索引,則不會影響資料的實際排列順序.就像書後面的附錄,雖然是按字母ABC...來排序,但書中內容並不是按這個序列一樣,不過
當我們要查詢資料時,卻可以從索引中快速找到所需的資料.

由於非叢集索引不會影響資料的實際排列順序,因此我們可以在資料表中設定多個非叢集索引.而叢集索引則最多隻能設定一個,因為實際
資料只能有一種排列順序.

索引值是否唯一:
如果索引值設為唯一(不可重復),則成為唯一索引(unique index),表示資料表中任何兩筆記錄的索引值都不可以相同,此與資料表的
Primary key 一樣.實際上,唯一索引最常使用在PRIMARY KEY 的欄位上,以區分每一筆記錄.設成唯一索引的欄位最好也設為not null
,否則只能輸入一筆為NULL的資料,再輸第二筆NULL的資料時,便會發生錯誤.

是否只用單一欄位做索引:
如果使用兩個或多個欄位組合起來做索引,則成為復合索引(composite index).如果復合索引同時也是唯一索引的話,那麼多個欄位
組合起來的值就不可重復,但單獨欄位可允許重復.不過注意,SQL Server只有當我們使用復合索引的第一個欄位有關的查詢時才會利用該索引來
查詢資料.例如,我們用"姓"或"姓+名"查詢會使用索引,但若用"名"來查詢就無法使用索引了.

系統自動建立的索引:
在SQL Server中,索引不一定要由資料庫設計者來自己建立.如果在建立資料表時,設定了Primary key或 unique條件約束時,SQL Server會自動
幫我們建好索引.
當資料表有欄位設成unique時,系統會用此欄位自動建立一個非叢集索引的唯一索引,以確保此欄位的唯一性.索引名稱為UQ_資料表名稱_xxxxxxxx
create table table_1(
ID smallint not null,
ProductName char(30) unique,
Price smallmoney
)
忽略重復的索引鍵:
當索引具有唯一性時,若選忽略重復的索引鍵時,若加入一樣的資料,雖然INSERT 會被執行,但也會自動取消這筆新加入的記錄.若設為否,
則insert敘述會出現錯誤而不會被執行.

重新計算統計資料:
在建立索引時,會預設建立此索引欄位的統計資料,以決定要如何使用索引來查詢資料,發揮最高的效率.當記錄有所改變時,該欄位的統計資料就
不是最新的情況,因此SQL Server會自動重新統計.

若設為否,表示不要讓SQL Server自動更新索引欄位的統計資料,此時對資料表做查詢時,可能無法達到最好的效率.因此建議設為是.讓SQL SERVER
去自動維護索引的統計資料.要檢視索引的統計結果.用DBCC SHOW_STATISTICS

DBCC SHOW_STATISTICS(table_name,index_name)

索引頁預留空間:
在預設情況下,每個中間節點索引頁都會只留下一個位子供新增的索引存放.若開啟索引頁預留空間選項,則可指定中間節點索引頁的填滿比率
要和填滿因數相同.由於此時索引頁預留空間是使用填滿因數的比率來調整,所以有指定填滿因子時,索引頁預留空間才有用處.

填滿因數:
建立索引時,每個末節點中填入資料的填滿比率.填滿因數越小則每個末節點存放的資料越少.加入為40,則在建立索引時,每個末節點頁只使用
40%的空間放置索引,而留下60%的空間不用.以存放日後新增的索引.沒有設定時,預設為0,但0和100是同一意思,表示末節點的索引頁不會留下
任何空間而全部填滿.

建立索引的注意事項:
1.一個資料表只能有一個叢集索引,因為資料表會依照叢集索引來排序其內的記錄.在必要時,我們可以將多個欄位組合起來作為叢集索引.

2.一個索引使用的欄位最多隻能包含16個欄位.而且Ntext,text以及image型別的欄位不能做索引(這些欄位衍生的一些計算欄位,其使用的不是
這三種型別,則這些欄位還是可以當成索引的內含資料行)只有非叢集索引,才可有內含資料行(內含資料行是經常會隨索引一起被查詢的資料行)

3.作為索引 的欄位(一個或多個)總長度限制在900 bytes以內,因此若某些欄位的加總長度超多限制就不能當索引

4.一個資料表最多隻能有249個非叢集索引

5.如果資料表的內容很少時.假如只有10筆的記錄,那麼除了所謂primary key 和unique欄位的索引外,不建議再增加其他索引.因為資料
查閱索引時要花一些額外的時間,而在資料不多的情況下,其效率可能比一筆一筆找來的差呢.

6.如果欄位的內容同性質很高.如性別只有男,女.那麼就不適合做索引,因為透過索引仍會找出一大堆的資料,還不如一筆一筆找

 

create table table_3

(

c1 int not null primary key,

c2 char(4),

c3 char(6),

c4 char(30)

)

--建立最基本的索引

create index MyIndex_1

on table_3(c1)

create index MyIndex_2

on table_3(c2,c3)

--檢視資料表中有哪些索引

 

exec sp_helpindex table_3

--建立索引的完整語法

 

create unique  nonclustered index index_3 on table_3

(c2) include (c3)

with pad_index,fillfactor=30,ignore_dup_key

 

--一般的索引刪除

 

drop index table_3.index_3

--系統自動產生的索引刪除

 

alter table table_3 drop constraint PK__table_3__5070F446

 

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7899089/viewspace-611472/，如需轉載，請註明出處，否則將追究法律責任。