第7章 索引與數(shù)據(jù)完整性

SQLServer2005

數(shù)據(jù)庫管理與開發(fā)教程第7章索引與數(shù)據(jù)完整性本章主要介紹索引與數(shù)據(jù)完整性，包括索引的概念、索引的建立、索引的刪除、索引的分析與維護(hù)、數(shù)據(jù)完整性及其實(shí)現(xiàn)機(jī)制。通過本章的學(xué)習(xí)，讀者應(yīng)掌握建立或者刪除索引的方法，能夠使用索引優(yōu)化數(shù)據(jù)庫查詢；了解數(shù)據(jù)完整性。7.1索引7.2數(shù)據(jù)完整性7.1索引

7.1.1索引的概念數(shù)據(jù)庫索引是對數(shù)據(jù)表中一個或多個列的值進(jìn)行排序的結(jié)構(gòu)，它是數(shù)據(jù)庫中一個非常有用的對象，就像一本書的索引，索引提供了在表中快速查詢特定行的能力。在表中索引的支持下，SQLServer查詢優(yōu)化器可以找出并使用正確的索引來優(yōu)化對數(shù)據(jù)的訪問。如果沒有索引，查詢優(yōu)化器只有一個選擇，那就是對表中的數(shù)據(jù)進(jìn)行全部掃描以找出要找的數(shù)據(jù)行。

7.1.2索引的建立

1．使用圖形界面創(chuàng)建索引圖7.1選擇新建索引圖7.2設(shè)置索引圖7.3添加表列圖7.4選擇排序順序

2．使用SQL語句創(chuàng)建索引

SQLServer2005可以自動創(chuàng)建索引，以滿足強(qiáng)制實(shí)施PRIMARYKEY和UNIQUE約束的唯一性要求。如果需要創(chuàng)建不依賴于約束的索引，可以使用CREATEINDEX語句來完成。其語法結(jié)構(gòu)如下。CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_name

ON<object>(column[ASC|DESC][,...n])[INCLUDE(column_name[,...n])][WITH(<relational_index_option>[,...n])][ON{partition_scheme_name(column_name)|filegroup_name

|default}][;]圖7.5查看新建索引

7.1.3索引的刪除刪除不再需要的索引，可以回收索引當(dāng)前使用的磁盤空間，避免不必要的浪費(fèi)。下面分別介紹使用SQLServerManagementStudio和SQL語句刪除索引的方法。

1．使用圖形界面刪除索引

圖7.6刪除索引

2．使用SQL語句刪除索引使用Transact-SQL語句中的DropIndex命令可刪除索引，其語法結(jié)構(gòu)如下：DROPINDEX<table_name>.<index_name>

7.1.4索引的分析與維護(hù)

1．索引的分析（1）使用SHOWPLAN語句顯示查詢語句的執(zhí)行信息，包含查詢過程中連接表時所采取的每個步驟以及選擇哪個索引。語法：SETSHOWPLAN_ALL{ON|OFF}SETSHOWPLAN_TEXT{ON|OFF}（2）使用STATISTICSIO語句顯示執(zhí)行數(shù)據(jù)檢索語句所花費(fèi)的磁盤活動量信息，可以利用這些信息來確定是否重新設(shè)計(jì)索引。語法：SETSTATISTICSIO{ON|OFF}

2．索引的維護(hù)（1）使用DBCCSHOWCONTIG語句顯示指定表的數(shù)據(jù)和索引的碎片信息。當(dāng)對表進(jìn)行大量的修改或添加數(shù)據(jù)后，應(yīng)該執(zhí)行此語句來查看有無碎片。語法：DBCCSHOWCONTIG[{table_name|table_id|view_name|

view_id},index_name|index_id])]

（2）使用DBCCDBREINDEX語句

重建指定數(shù)據(jù)庫中表的一個或多個索引。語法：DBCCDBREINDEX(['database.owner.table_name'[,index_name[,fillfactor]]])[WITHNO_INFOMSGS]（3）使用DBCCINDEXDEFRAG語句

整理指定的表或視圖的聚集索引和輔助索引碎片。語法：DBCCINDEXDEFRAG({database_name|database_id|0},{table_name|table_id|'view_name'|view_id},{index_name|index_id})[WITHNO_INFOMSGS]7.2數(shù)據(jù)完整性在SQLServer2005中，數(shù)據(jù)庫主要用于存儲數(shù)據(jù)及其相關(guān)的對象（表、視圖及存儲過程等），如何確保輸入的數(shù)據(jù)符合規(guī)則，是保證數(shù)據(jù)庫正常使用的前提條件。本節(jié)主要講述數(shù)據(jù)完整性的相關(guān)知識。

7.2.1數(shù)據(jù)完整性概述數(shù)據(jù)完整性是SQLServer用于保證數(shù)據(jù)庫中數(shù)據(jù)一致性的一種機(jī)制，防止非法數(shù)據(jù)存入數(shù)據(jù)庫。數(shù)據(jù)完整性主要體現(xiàn)在以下幾點(diǎn)。（1）數(shù)據(jù)類型準(zhǔn)確無誤。（2）數(shù)據(jù)取值符合規(guī)定的范圍。（3）多個數(shù)據(jù)表之間的數(shù)據(jù)不存在沖突。

1．實(shí)體完整性現(xiàn)實(shí)世界中，任何一個實(shí)體都有區(qū)別于其他實(shí)體的特征，即實(shí)體完整性。在SQLServer數(shù)據(jù)庫中，實(shí)體完整性是指所有的記錄都應(yīng)該有一個唯一的標(biāo)識，以確保數(shù)據(jù)表中數(shù)據(jù)的唯一性。如果將數(shù)據(jù)庫中數(shù)據(jù)表的第一行看作一個實(shí)體，可以通過以下幾項(xiàng)實(shí)現(xiàn)實(shí)體完整性。（1）唯一索引（UniqueIndex）。（2）主鍵（PrimaryKey）。（3）唯一碼（UniqueKey）。（4）標(biāo)識列（IdentityColumn）。

2．域完整性域是指數(shù)據(jù)表中的列（字段），域完整性就是指列的完整性。它要求數(shù)據(jù)表中指定列的數(shù)據(jù)具有正確的數(shù)據(jù)類型、格式和有效的數(shù)據(jù)范圍。域完整性常見的實(shí)現(xiàn)機(jī)制包括以下幾點(diǎn)。（1）默認(rèn)值（Default）。（2）檢查（Check）。（3）外鍵（ForeignKey）。（4）數(shù)據(jù)類型（DataType）。（5）規(guī)則（Rule）。

3．引用完整性引用完整性又稱參照完整性，通過主鍵（PrimaryKey）約束和外鍵（FOREIGNKEY）約束來實(shí)現(xiàn)被參照表和參照表之間的數(shù)據(jù)一致性。引用完整性可以確保鍵值在所有表中保持一致，如果鍵值更改了，在整個數(shù)據(jù)庫中，對該鍵值的所有引用要進(jìn)行一致的更改。強(qiáng)制引用完整性時，SQLServer禁止用戶進(jìn)行下列操作。（1）當(dāng)主表中沒有關(guān)聯(lián)的記錄時，將記錄添加到相關(guān)表中。（2）更改主表中的值并導(dǎo)致相關(guān)表中的記錄孤立。（3）從主表中刪除記錄，但仍存在與該記錄匹配的相關(guān)記錄。

4．用戶定義完整性用戶定義完整性是用戶希望定義的除實(shí)體完整性、域完整性和參照完整性之外的數(shù)據(jù)完整性。它反映某一具體應(yīng)用所涉及的數(shù)據(jù)必須滿足的語義要求。

SQLServer提供了定義和檢驗(yàn)這類完整性的機(jī)制。（1）規(guī)則（Rule）。（2）觸發(fā)器（Trigger）。（3）存儲過程（StoredProcedure）。（4）創(chuàng)建數(shù)據(jù)表時的所有約束（Constraint）。

7.2.2實(shí)現(xiàn)數(shù)據(jù)完整性

1．規(guī)則規(guī)則是對錄入數(shù)據(jù)列中的數(shù)據(jù)所實(shí)施的完整性約束條件，它指定了插入到數(shù)據(jù)列中的可能值。其特點(diǎn)主要體現(xiàn)在以下幾點(diǎn)。（1）規(guī)則是SQLServer2005數(shù)據(jù)庫中獨(dú)立于表、視圖和索引的數(shù)據(jù)對象，刪除表不會刪除規(guī)則。（2）一個列上可以使用多個規(guī)則。

2．默認(rèn)值如果在插入行時沒有指定列的值，那么將指定列中所使用的值為默認(rèn)值。默認(rèn)值可以是任何取值為常量的對象，如內(nèi)置函數(shù)和數(shù)學(xué)表達(dá)式等。通過以下兩種方法可以設(shè)定默認(rèn)值。（1）在CREATETABLE中使用DEFAULT關(guān)鍵字創(chuàng)建默認(rèn)定義，將常量表達(dá)式指派為列的默認(rèn)值，這是標(biāo)準(zhǔn)方法。（2）使用CREATEDEFAULT語句創(chuàng)建默認(rèn)對象，然后使用sp_bindefault系統(tǒng)存儲過程將它綁定到列上，這是一個向前兼容的功能。

【例7-1】在表中對應(yīng)字段使用默認(rèn)值。圖7.7表設(shè)計(jì)器

3．約束約束（Constraint）是數(shù)據(jù)庫服務(wù)器強(qiáng)制用戶必須遵從的業(yè)務(wù)邏輯。它定義了列允許的取值，限制用戶可能輸入指定列的值，從而強(qiáng)制引用完整性。約束是強(qiáng)制完整性的標(biāo)準(zhǔn)機(jī)制。

SQLServer2005中的約束機(jī)制包括以下5種。（1）非空約束（NOTNULL）（2）檢查約束（CHECK約束）（3）唯一約束（UNIQUE約束）（4）主鍵約束（PRIMARYKEY約束）（5）外鍵約束（FOREIGNKEY約束）

【例7-2】針對表中字段使用CHECK約束。圖7.8CHE