創建索引

2024-07-21 02:13:02

字體：大中小

來源：轉載

供稿：網友

8.2.1 用create index 命令創建索引
create index 既可以創建一個可改變表的物理順序的簇索引，也可以創建提高查詢性能的非簇索引。其語法如下：
create [unique] [clustered | nonclustered]
index index_name on {table | view } column [ asc | desc ] [,...n])
[with
[pad_index]
[ [, ] fillfactor = fillfactor]
[ [, ] ignore_dup_key]
[ [, ] drop_existing]
[ [, ] statistics_norecompute]
[ [, ] sort_in_tempdb ]
]
[on filegroup]
各參數說明如下：

unique
創建一個惟一索引，即索引的鍵值不重復。在列包含重復值時，不能建惟一索引。如要使用此選項，則應確定索引所包含的列均不允許null 值，否則在使用時會經常出錯。
clustered
指明創建的索引為簇索引。如果此選項缺省，則創建的索引為非簇索引。
nonclustered
指明創建的索引為非簇索引其索引。數據頁中包含了指向數據庫中實際的表數據頁的指針。
index_name
指定所創建的索引的名稱。索引名稱在一個表中應是惟一的，但在同一數據庫或不同數據庫中可以重復。
table
指定創建索引的表的名稱。必要時還應指明數據庫名稱和所有者名稱。
view
指定創建索引的視圖的名稱。視圖必須是使用schemabinding 選項定義過的，其具體信息請參見“視圖創建”章節。
asc | desc
指定特定的索引列的排序方式。默認值是升序（asc）。
column
指定被索引的列。如果使用兩個或兩個以上的列組成一個索引，則稱為復合索引。一個索引中最多可以指定16 個列，但列的數據類型的長度和不能超過900 個字節。
pad_index
指定填充索引的內部節點的行數，至少應大于等于兩行。pad_index 選項只有在fillfactor 選項指定后才起作用。因為pad_index 使用與fillfactor 相同的百分比。缺省時，sql server 確保每個索引頁至少有能容納一條最大索引行數據的空閑空間。如果fillfactor 指定的百分比不夠容納一行數據s，ql server 會自動內部更改百分比。
fillfactor = fillfactor
fillfactor 稱為填充因子，它指定創建索引時，每個索引頁的數據占索引頁大小的百分比，fillfactor 的值為1 到100。它其實同時指出了索引頁保留的自由空間占索引頁大小的百分比。即100 - fillfactor。對于那些頻繁進行大量數據插入或刪除的表在建索引時應該為將來生成的索引數據預留較大的空間，即將fillfactor 設得較小，否則，索引頁會因數據的插入而很快填滿，并產生分頁，而分頁會大大增加系統的開銷。但如果設得過小，又會浪費大量的磁盤空間，降低查詢性能。因此，對于此類表通常設一個大約為10 的fillfactor。而對于數據不更改的、高并發的、只讀的表，fillfactor 可以設到95 以上乃至100。

如果沒有指定此選項，sql server 默認其值為0 。0 是個特殊值，與其它小fillfactor
值（如：1，2）的意義不同，其葉節點頁被完全填滿，而在索引頁中還有一些空間。可以
用存儲過程sp_configure 來改變默認的fillfactor 值。
ignore_dup_key
此選項控制了當往包含于一個惟一約束中的列中插入重復數據時sql server 所作的反應。當選擇此選項時，sql server 返回一個錯誤信息，跳過此行數據的插入，繼續執行下面的插入數據的操作：當沒選擇此選項時，sql server 不僅會返回一個錯誤信息，還會回滾（rolls back）整個insert 語句（關于回滾，請參見“數據庫更新”章節中的“事務”一節）。
drop_existing
指定要刪除并重新創建簇索引。刪除簇索引會導致所有的非簇索引被重建，因為需要用行指針來替換簇索引鍵。如果再重建簇索引，那么非簇索引又會再重建一次，以便用簇索引鍵來替換行指針。使用drop_existing 選項可以使非簇索引只重建一次。
statistics_norecompute
指定分布統計不自動更新。需要手動執行不帶norecompute 子句的updatestatistics 命令。
sort_in_tempdb
指定用于創建索引的分類排序結果將被存儲到tempdb 數據庫中。如果tempdb 數據庫和用戶數據庫位于不同的磁盤設備上，那么使用這一選項可以減少創建索引的時間，但它會增加創建索引所需的磁盤空間。
on filegroup
指定存放索引的文件組。

注意：數據類型為text、ntext、image或bit的列不能作為索引的列。
由于索引的寬度不能超過900個字節，因此數據類型為char、varchar、binary和varbinary的列的列寬度超過了900字節，或數據類型為nchar、nvarchar的列的列寬度超過了450個字節時也不能作為索引的列。

在使用索引創建向導創建索引時，不能將計算列包含在索引中，但在直接創建或使用create index命令創建索引時，則可以對計算機列創建索引，這在sql server2000以前的版本中是不允許的，算得上是一大改進。
例8-1：為表products 創建一個簇索引。
create unique clustered index pk_p_id
on products(p_id)
with
pad_index,
fillfactor = 10,
ignore_dup_key,
drop_existing,
statistics_norecompute
on [primary]
例8-2 為表products 創建一個復合索引
create index pk_p_main
on products(p_id, p_name, sumvalue) --其中sumvalue 是一個計算列表達式為price*quantity
with
pad_index,
fillfactor = 50
on [primary]
例8-3 創建一個視圖并為它建一個索引
create view dbo.work_years
with
schemabinding
as
select top 100 percent emp_id,e_name, birthday, hire_date, year(getdate())
- year(hire_date) as work_years
from dbo.employee
order by work_years desc
create unique clustered
index emp_id_view on dbo.work_years (emp_id)

8.2.2 用enterprise manager 創建索引。
在enterprise manager 中創建索引有兩種方法
1 用索引創建向導創建索引
創建方法如下：
（1）在目錄樹中選擇要創建索引的表所在的數據庫，點擊任務板中的向導頁面
“wizards”，出現如圖8-2 所示的選擇向導界面。

圖8-2 選擇索引創建向導

（2）單擊任務板中的“create an index” 鏈接，出現如圖8-3 所示的索引創建向導界面。也可以從“tools ”菜單中選擇“izards” 菜單項，則出現如圖5-17 所示的選擇sql server 向導對話框，再從樹型目錄中選擇“database”下的“create index wizard”選項，也會出現如圖8-3 所示的索引創建向導界面。

（3）單擊“下一步”按鈕，出現如圖8-4 所示的界面，從中選擇要創建索引的表，及其所屬的數據庫。

（4）單擊“下一步”按鈕，出現如圖8-5 所示的界面，它顯示了所選擇的表中已經存在的索引的信息。如果表還沒有創建過索引，則不會出現此界面，而直接出現如圖8-6 所示的界面。

（5）單擊“下一步”按鈕，出現如圖8-6 所示的界面，它列出了表中的所有列的信息，從中選擇創建索引的列。

（6）單擊“下一步”按鈕，出現如圖8-7 所示的界面，它列出了可供選擇的索引設置選項（有關索引選項的設置，請參考前面講述的create index 命令）。

（7）單擊“下一步”按鈕，出現如圖8-8 所示的完成索引創建界面，在此可以指定所創建索引的名稱，還可以調整組成索引的列的順序。單擊“完成”按鈕，結束索引創建過程。系統會彈出一個創建索引成功信息對話框。

2 直接創建索引
選擇要創建索引的表，單擊右鍵，從快捷菜單中選擇“所有任務（all tasks）”子菜單中的“manage indexes”選項，將會出現如圖8-9 所示的索引管理對話框，其中列出了表中已經存在的索引。選擇“new” 按鈕，進入如圖8-10 所示的創建索引對話框。在圖8-10中，輸入要創建的索引的名稱，再選擇用于創建索引的列，并設置索引的各種選項（有關索引選項的設置，請參考前面講述的create index 命令）。單擊“ok” 按鈕，完成索引的創建。