ISBN978-7-111-50122-0第9章

數(shù)據(jù)完整性、規(guī)則和索引在SQLServer2012中創(chuàng)建數(shù)據(jù)庫(kù)和表，目的不僅僅是簡(jiǎn)單的存儲(chǔ)數(shù)據(jù)供用戶使用，更重要的是在保證數(shù)據(jù)的一致性和準(zhǔn)確性的前提下，高效的存儲(chǔ)、使用數(shù)據(jù)。設(shè)置完整性、規(guī)則和索引，可以幫助用戶實(shí)現(xiàn)在數(shù)據(jù)的一致性和準(zhǔn)確性的前提下，更高效的存儲(chǔ)、使用數(shù)據(jù)。本章主要介紹數(shù)據(jù)完整性、規(guī)則和索引的概念及使用。9.1數(shù)據(jù)完整性當(dāng)操作表中數(shù)據(jù)時(shí)，由于種種原因，經(jīng)常會(huì)遇到一些問(wèn)題。例如，Student表中StudentID列是不允許重復(fù)的。但有可能在工作人員輸入信息時(shí)誤操作，將兩個(gè)學(xué)生的StudentID列值輸入相同了?；蛘哂袀€(gè)學(xué)生的StudentID列值在Mark表中存在，但在Student表中找不到。如果當(dāng)時(shí)沒(méi)有發(fā)現(xiàn)，錯(cuò)誤將一直存在，這樣下去將可能影響其他一些信息存儲(chǔ)的錯(cuò)誤。像這樣的錯(cuò)誤如果由人工來(lái)檢查，學(xué)校學(xué)生人多眾多，檢查的效果是難以想象的。這就要求數(shù)據(jù)庫(kù)管理系統(tǒng)能自動(dòng)實(shí)現(xiàn)這樣的錯(cuò)誤校正。數(shù)據(jù)完整性是指存儲(chǔ)數(shù)據(jù)庫(kù)的數(shù)據(jù)的一致性和準(zhǔn)確性。數(shù)據(jù)完整性包括實(shí)體完整性、參照完整性和用戶自定義完整性。SQLServer2012提供了強(qiáng)大的數(shù)據(jù)完整性功能，也分為3類：實(shí)體完整性、域完整性和引用完整性。在SQLServer2012中，將完整性約束又稱為表約束。9.1.1實(shí)體完整性1.主鍵約束第6章中已經(jīng)介紹了，在表結(jié)構(gòu)設(shè)計(jì)器中可以設(shè)置主鍵。某列（例如StudentID列、CourseID、AuthorID列）被設(shè)置為主鍵，該列取值就不能為空，也不能重復(fù)。當(dāng)表中主鍵列值違反主鍵約束，即出現(xiàn)重復(fù)值或有空值存在，系統(tǒng)自動(dòng)提示錯(cuò)誤信息。例如當(dāng)在Course表中輸入一門新課，如果CourseID號(hào)與其他數(shù)據(jù)重復(fù)，違反了主鍵約束，系統(tǒng)將提示出錯(cuò)，如圖所示。主鍵約束是表對(duì)象，在表的“鍵”中可以查看。如圖所示。9.1.1實(shí)體完整性【例9-1】

使用CREATETABLE語(yǔ)句創(chuàng)建Course1表。USECOLLEGEGOCREATETABLECourse1(CourseIDNCHAR(3)NOTNULLPRIMARYKEY,CourseNameNCHAR(20)NOTNULL,CreditINTNULL,)GOCourseID被設(shè)置為主鍵，需要PRIMARYKEY字句。PRIMARYKEY字句可以放在列名后面，也可以放在最后?！纠?-2】

使用CREATETABLE語(yǔ)句創(chuàng)建Mark1表。USECOLLEGEGOCREATETABLEMark1(CourseIDNCHAR(3)NOTNULL,StudentIDNCHAR(10)NOTNULL,ScoreINTNULL,CONSTRAINTPK_Mark1PRIMARYKEY(CourseID,StudentID))GOCourseID和StudentID被設(shè)置為主鍵（組合多列主鍵），PRIMARYKEY就不能放在列名后，只能放在最后。CONSTRAINT字句用來(lái)命名主鍵，也可以如【例9-1】一樣省略。當(dāng)使用INSERT、UPDATE語(yǔ)句違反主鍵約束時(shí)，系統(tǒng)也會(huì)提示錯(cuò)誤信息。9.1.1實(shí)體完整性【例9-3】

使用INSERT語(yǔ)句給Course表添加新記錄。USECOLLEGEGOINSERTCourseVALUES('226','數(shù)據(jù)結(jié)構(gòu)',5)GO如果CourseID列值與其他數(shù)據(jù)重復(fù)，違反了主鍵約束，系統(tǒng)將提示出錯(cuò)。9.1.1實(shí)體完整性2.唯一性約束一個(gè)表只能有一個(gè)主鍵。但有的表中除了主鍵不能為空和不能有重復(fù)值存在之外，還有其它列也有同樣的要求，例如Course表的CourseName列、School表的SchoolName列也不允許為空和不能有重復(fù)值存在。這時(shí)就可以設(shè)置唯一性來(lái)實(shí)現(xiàn)。9.1.1實(shí)體完整性唯一性約束是表對(duì)象，在表的“鍵”中可以查看。如圖所示。主鍵約束和唯一性約束都在表的“鍵”中以鍵對(duì)象的形式存在。它們不僅以名稱區(qū)分，還以圖標(biāo)顯示區(qū)分。

圖標(biāo)（金黃色）表示主鍵對(duì)象，

圖標(biāo)（藍(lán)色）表示唯一性約束對(duì)象。9.1.1實(shí)體完整性如果唯一性約束設(shè)置需要修改或刪除，可以在表結(jié)構(gòu)設(shè)計(jì)器中修改或刪除，也可以針對(duì)鍵對(duì)象修改或刪除。如圖所示。當(dāng)用戶在School表中輸入一個(gè)新學(xué)院，如果SchoolName與其他數(shù)據(jù)重復(fù)，違反了唯一性約束，系統(tǒng)將提示出錯(cuò)，如圖所示。9.1.1實(shí)體完整性使用T-SQL語(yǔ)句也可以實(shí)現(xiàn)唯一性約束。在CREATETABLE或ALTERTABLE語(yǔ)句中，使用UNIQUE子句，實(shí)現(xiàn)唯一性約束的創(chuàng)建、修改或刪除?！纠?-4】

使用ALTERTABLE語(yǔ)句修改Course表。USECOLLEGEGOALTERTABLECourseADDCONSTRAINTIX_CourseUNIQUE(CourseName)GO當(dāng)使用INSERT、UPDATE語(yǔ)句違反唯一性約束時(shí)，系統(tǒng)也會(huì)提示錯(cuò)誤信息。主鍵約束和唯一性約束從約束效果看，基本相同。不同的是，一個(gè)表只能有一個(gè)主鍵約束，但可以有多個(gè)唯一性約束。9.1.2域完整性域完整性也稱為列完整性，是指一個(gè)數(shù)據(jù)集對(duì)某一個(gè)列是否有效和確定是否允許為空值。在SQLServer2012中，域完整性可以通過(guò)空值約束、默認(rèn)約束和檢查約束實(shí)現(xiàn)。1.空值約束在第6章中已經(jīng)介紹了，在表結(jié)構(gòu)設(shè)計(jì)器中可以設(shè)置“允許Null值”。某列被設(shè)置為“NULL”或“NOTNULL”，該列取值就可以為空或不能為空。一旦某列被設(shè)置為主鍵或唯一性索引，系統(tǒng)自動(dòng)將其設(shè)置為“NOTNULL”。當(dāng)表中某列被設(shè)置為“NOTNULL”，而其值違反約束，系統(tǒng)自動(dòng)提示錯(cuò)誤信息。9.1.2域完整性如果空值約束設(shè)置需要修改或刪除，可以在表結(jié)構(gòu)設(shè)計(jì)器中修改或刪除。使用T-SQL語(yǔ)句也可以實(shí)現(xiàn)空值約束。在CREATETABLE或ALTERTABLE語(yǔ)句中，使用NULL或NOTNULL，實(shí)現(xiàn)空值約束的創(chuàng)建、修改或刪除。9.1.2域完整性2.默認(rèn)約束默認(rèn)約束也稱為默認(rèn)值約束，在第6章中也已經(jīng)介紹了。當(dāng)某列（例如Sex列）設(shè)置默認(rèn)值，即設(shè)置了默認(rèn)約束。該列取值可以是新輸入的數(shù)據(jù)，如果沒(méi)有則取默認(rèn)值。默認(rèn)約束是表對(duì)象，在表的“約束”中可以查看。如果默認(rèn)約束對(duì)象需要重命名或刪除，可以選擇右鍵菜單選項(xiàng)。如果修改默認(rèn)約束的值，可以在表結(jié)構(gòu)設(shè)計(jì)器中修改或刪除。9.1.2域完整性使用T-SQL語(yǔ)句也可以實(shí)現(xiàn)默認(rèn)約束。在CREATETABLE或ALTERTABLE語(yǔ)句中，使用DEFAULT子句實(shí)現(xiàn)默認(rèn)約束的創(chuàng)建、修改或刪除?！纠?-5】

使用CREATETABLE語(yǔ)句創(chuàng)建Student1表。USECOLLEGEGOCREATETABLEStudent1(StuIDINTPRIMARYKEY,NameNCHAR(10),SexNCHAR(10)DEFAULT('女') /*設(shè)置默認(rèn)約束*/)GO【例9-6】

使用ALTERTABLE語(yǔ)句修改Course表。USECOLLEGEGOALTERTABLECourseADDCONSTRAINTDE_Course_CreditDEFAULT4FORCredit /*添加默認(rèn)約束*/GO9.1.2域完整性3.檢查約束例如在Student表中，Sex列只能取值“男”或“女”。如果誤輸入其它值則肯定錯(cuò)誤。又例如在Mark表中，Score列通常只能取值0~100，超出范圍也錯(cuò)誤。這些錯(cuò)誤都是邏輯性錯(cuò)誤。這時(shí)就可以設(shè)置檢查約束來(lái)進(jìn)行約束。檢查約束又稱為CHECK約束。9.1.2域完整性CHECK約束是表對(duì)象，在表的“約束”中可以查看。如果CHECK約束對(duì)象需要重命名或刪除，可以選擇右鍵菜單選項(xiàng)。如果修改CHECK約束的約束表達(dá)式，可以在“CHECK約束”對(duì)話框中操作。9.1.2域完整性使用T-SQL語(yǔ)句也可以實(shí)現(xiàn)CHECK約束。在CREATETABLE或ALTERTABLE語(yǔ)句中，使用CHECK子句實(shí)現(xiàn)?！纠?-7】

使用CREATETABLE語(yǔ)句創(chuàng)建Teacher表。USECOLLEGEGOCREATETABLETeacher(nameNCHAR(10),sexNCHAR(2)DEFAULT'男',CONSTRAINTCK_Emp_sexCHECK(sex='男'ORsex='女') /*創(chuàng)建CHECK約束*/)GO【例9-8】

使用ALTERTABLE語(yǔ)句修改Mark表。USECOLLEGEGOALTERTABLEMark/*添加CHECK約束*/ADDCONSTRAINTCK_Mark_ScoreCHECK(Score>=0andScore<=100)GO當(dāng)使用INSERT、UPDATE語(yǔ)句違反唯一性約束時(shí)，系統(tǒng)也會(huì)提示錯(cuò)誤信息。9.1.3引用完整性在表結(jié)構(gòu)設(shè)計(jì)器中設(shè)置外鍵關(guān)系，可以在主鍵表中進(jìn)行，也可在外鍵表中進(jìn)行。在SQLServer2012中通常在外鍵表中操作。例如打開(kāi)Mark表，在表結(jié)構(gòu)設(shè)計(jì)器，選擇右鍵菜單“關(guān)系”選項(xiàng)。進(jìn)入“外鍵關(guān)系”對(duì)話框。選擇“添加”按鈕，添加一個(gè)新外部關(guān)系對(duì)象。9.1.3引用完整性選擇“表和列規(guī)范”選項(xiàng)，彈出“表和列”對(duì)話框。用戶選擇主鍵表和主鍵列，以及外鍵表和外部鍵列。注意，設(shè)置時(shí)主鍵表和外鍵表的列應(yīng)該對(duì)應(yīng)（例如Student表的StudentID列對(duì)應(yīng)Mark表的StudentID列），而且列數(shù)也應(yīng)相同。9.1.3引用完整性使用T-SQL語(yǔ)句也可以實(shí)現(xiàn)外部約束關(guān)系。在ALTERTABLE語(yǔ)句中，使用FOREIGNKEY子句和REFERENCES子句實(shí)現(xiàn)?！纠?-9】

使用CREATETABLE語(yǔ)句創(chuàng)建Mark2表，同時(shí)創(chuàng)建和Course表的外鍵關(guān)系。USECOLLEGEGOCREATETABLEMark2(CourseIDNCHAR(3),StudentIDNCHAR(10),ScoreINT,CONSTRAINTPK_Mark1_CourseFOREIGNKEY(CourseID)REFERENCESCourse(CourseID))GO使用T-SQL語(yǔ)句創(chuàng)建外鍵關(guān)系，F(xiàn)OREIGNKEY子句后面指定本表的外部鍵對(duì)應(yīng)的列名，REFERENCES子句后面指定主鍵表表名和主鍵的列名。當(dāng)使用INSERT、UPDATE、DELETE語(yǔ)句違反唯一性約束時(shí)，系統(tǒng)也會(huì)提示錯(cuò)誤信息。9.2規(guī)則規(guī)則（RULES）不是數(shù)據(jù)庫(kù)中必須定義的對(duì)象，但定義規(guī)則可以保證表中的數(shù)據(jù)都能滿足設(shè)計(jì)者的要求。雖然規(guī)則功能強(qiáng)大，在SQLServer系列中一直存在，但微軟公司在SQLServer2012聯(lián)機(jī)幫助中說(shuō)明，規(guī)則將在后續(xù)版本中刪除，建議用戶使用檢查約束。所以本書(shū)只做簡(jiǎn)單介紹。9.2.1規(guī)則的概念規(guī)則是用于執(zhí)行一些與檢查約束相同的功能，但檢查約束比規(guī)則更簡(jiǎn)明。一個(gè)列或別名數(shù)據(jù)類型只能被綁定一個(gè)規(guī)則。不過(guò)，一個(gè)列可以同時(shí)有一個(gè)規(guī)則以及一個(gè)或多個(gè)檢查約束與其相關(guān)聯(lián)。在這種情況下，SQLServer2012將評(píng)估所有限制。約束是某一個(gè)表的對(duì)象，只作用于某一個(gè)表。規(guī)則是數(shù)據(jù)庫(kù)對(duì)象，作用于整個(gè)數(shù)據(jù)庫(kù)。CHECK約束被指定為CREATETABLE語(yǔ)句或ALTERTABLE語(yǔ)句的一部分，而規(guī)則作為獨(dú)立的對(duì)象創(chuàng)建，然后綁定在指定的列上。規(guī)則也是一種維護(hù)數(shù)據(jù)庫(kù)中數(shù)據(jù)完整性的手段，使用它可以避免表中出現(xiàn)不符合邏輯的數(shù)據(jù)。9.2.2創(chuàng)建規(guī)則創(chuàng)建規(guī)則只能使用T-SQL語(yǔ)句命令方式，而且創(chuàng)建規(guī)則只能在當(dāng)前的數(shù)據(jù)庫(kù)中創(chuàng)建。。T-SQL語(yǔ)句提供了規(guī)則創(chuàng)建語(yǔ)句CREATERULE，但是不能在單個(gè)批處理中將CREATERULE語(yǔ)句與其他T-SQL語(yǔ)句組合在一起。其語(yǔ)法格式如下：CREATERULE[schema_name.]rule_nameAScondition_expression[;]【例9-8】

使用CREATERULE語(yǔ)句創(chuàng)建規(guī)則，指定變量取值在0~100之間。USECOLLEGEGOCREATERULEScore_RullAS@s_rBETWEEN0AND100GO在當(dāng)前數(shù)據(jù)庫(kù)的可編程性對(duì)象的規(guī)則對(duì)象中，新建一個(gè)名為“Score_Rull”的規(guī)則對(duì)象。9.2.3查看規(guī)則創(chuàng)建規(guī)則只能使用T-SQL語(yǔ)句命令方式創(chuàng)建，但可以在SQLServerManagementStudio的對(duì)象資源管理器中看到規(guī)則。也可以調(diào)用相關(guān)系統(tǒng)存儲(chǔ)過(guò)程，例如執(zhí)行p_help系統(tǒng)存儲(chǔ)過(guò)程獲得關(guān)于規(guī)則的報(bào)告，如圖9-30所示。以規(guī)則名稱作為參數(shù)來(lái)執(zhí)行sp_helptext系統(tǒng)存儲(chǔ)過(guò)程顯示規(guī)則的文本，如圖9-31所示。若要重命名規(guī)則，可以執(zhí)行sp_rename系統(tǒng)存儲(chǔ)過(guò)程。9.2.4綁定規(guī)則綁定規(guī)則是指將已經(jīng)存在的規(guī)則應(yīng)用到列或用戶自定義的數(shù)據(jù)類型中。創(chuàng)建規(guī)則后，只有將規(guī)則綁定到列，規(guī)則才會(huì)起到約束功能。執(zhí)行sp_bindrule系統(tǒng)存儲(chǔ)過(guò)程可將規(guī)則綁定到列或別名數(shù)據(jù)類型。規(guī)則必須與列數(shù)據(jù)類型兼容。只有當(dāng)嘗試在別名數(shù)據(jù)類型的數(shù)據(jù)庫(kù)列中插入值或進(jìn)行更新時(shí)，綁定到別名數(shù)據(jù)類型的規(guī)則才會(huì)激活。其語(yǔ)法格式如下：sp_bindrule[@rulename=]'rule',[@objname=]'object_name'[,[@futureonly=]'futureonly_flag']【例9-9】

執(zhí)行sp_bindrule系統(tǒng)存儲(chǔ)過(guò)程綁定規(guī)則。USECOLLEGEGOEXECsp_bindrule'Score_Rull','Mark.Score'GO9.2.5解除規(guī)則如果某列已經(jīng)不適用于某規(guī)則，則可以解除規(guī)則。執(zhí)行存儲(chǔ)過(guò)程sp_unbindrule可以將規(guī)則從綁定列或用戶自定義的數(shù)據(jù)類型解除。其語(yǔ)法格式如下：sp_unbindrule[@objname=]'object_name'[,[@futureonly=]'futureonly_flag']【例9-10】

執(zhí)行sp_unbindrule解除規(guī)則。USECOLLEGEGOEXECsp_unbindrule'Mark.Score'GO9.2.6刪除規(guī)則如果此數(shù)據(jù)庫(kù)不需要某規(guī)則，則可以刪除。在創(chuàng)建同名的新規(guī)則之前，必須首先刪除原有規(guī)則，而在刪除原有規(guī)則之前，必須先解除綁定?？梢赃x擇右鍵菜單的“刪除”選項(xiàng)刪除規(guī)則，也可以執(zhí)行DROPRULE語(yǔ)句刪除。其語(yǔ)法格式如下：DROPRULE{[schema_name.]rule_name}[,...n][;]【例9-11】

執(zhí)行CREATERULE語(yǔ)句刪除規(guī)則。USECOLLEGEGODROPRULEScore_RullGO9.3索引索引是關(guān)系數(shù)據(jù)庫(kù)的一個(gè)基本概念。用戶使用數(shù)據(jù)庫(kù)最常用的操作就是查詢數(shù)據(jù)，在數(shù)據(jù)量比較大時(shí)，搜索滿足條件的數(shù)據(jù)可能需要很長(zhǎng)的時(shí)間。為了提高數(shù)據(jù)檢索的能力，數(shù)據(jù)庫(kù)中引入了索引的概念。索引如同書(shū)籍的目錄，有了索引，用戶可以快速找到表或索引視圖中的特定信息。索引包含從表或視圖中一個(gè)或多個(gè)列生成的鍵，以及映射到指定數(shù)據(jù)的存儲(chǔ)位置的指針。通過(guò)創(chuàng)建設(shè)計(jì)良好的索引以支持查詢，可以提高查詢性能。對(duì)于包含SELECT、UPDATE或DELETE語(yǔ)句的各種查詢，索引會(huì)很有用。索引還可以強(qiáng)制表中的行具有唯一性，從而確保表數(shù)據(jù)的完整性。SQLServer2012在存儲(chǔ)數(shù)據(jù)時(shí)，數(shù)據(jù)按照輸入的時(shí)間順序被放置在數(shù)據(jù)頁(yè)上。一般情況下，數(shù)據(jù)存放的順序與數(shù)據(jù)本身是沒(méi)有任何聯(lián)系的。而索引是與表或視圖關(guān)聯(lián)的磁盤上的結(jié)構(gòu)，可以加快從表或視圖中檢索行的速度。索引包含由表或視圖中的一列或多列生成的鍵，使SQLServer2012可以快速有效地查找與鍵值關(guān)聯(lián)的行。9.3索引索引主要有以下作用：1）快速存取、查詢數(shù)據(jù)。2）保證數(shù)據(jù)的一致性。3）實(shí)現(xiàn)表與表之間的參照完整性。4）在使用GROUPBY、ORDERBY子句進(jìn)行查詢時(shí)，利用索引可以減少排序和分組的時(shí)間。但索引也有自身的缺點(diǎn)：1）索引和維護(hù)索引要耗費(fèi)時(shí)間。2）索引需要占用物理存儲(chǔ)空間。3）當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行添加、修改和刪除時(shí)，索引也要?jiǎng)討B(tài)維護(hù)。因此，沒(méi)有必要對(duì)表中所有列建立索引，而應(yīng)該根據(jù)實(shí)際需要建立索引。9.3.1索引的分類如果一個(gè)表沒(méi)有創(chuàng)建索引，則數(shù)據(jù)行不按任何特定順序存儲(chǔ)，這種結(jié)構(gòu)稱為堆集。SQLServer2012支持在表中任何列（包括計(jì)算列）上定義索引。SQLServer中可用的索引類型，如表9-1所示。表9-1索引類型表索引類型說(shuō)明聚集聚集索引基于聚集索引鍵按順序排序和存儲(chǔ)表或視圖中的數(shù)據(jù)行。聚集索引按B樹(shù)索引結(jié)構(gòu)實(shí)現(xiàn)，B樹(shù)索引結(jié)構(gòu)支持基于聚集索引鍵值對(duì)行進(jìn)行快速檢索。非聚集既可以使用聚集索引來(lái)為表或視圖定義非聚集索引，也可以根據(jù)堆來(lái)定義非聚集索引。非聚集索引中的每個(gè)索引行都包含非聚集鍵值和行定位符。此定位符指向聚集索引或堆中包含該鍵值的數(shù)據(jù)行。索引中的行按索引鍵值的順序存儲(chǔ)，但是不保證數(shù)據(jù)行按任何特定順序存儲(chǔ)，除非對(duì)表創(chuàng)建聚集索引。唯一唯一索引確保索引鍵不包含重復(fù)的值，因此，表或視圖中的每一行在某種程度上是唯一的。唯一性可以是聚集索引和非聚集索引的屬性。

列存儲(chǔ)一種基于按列對(duì)數(shù)據(jù)進(jìn)行垂直分區(qū)的xVelocity內(nèi)存優(yōu)化列存儲(chǔ)索引，作為大型對(duì)象（LOB）存儲(chǔ)。帶有包含列的索引一種非聚集索引，它擴(kuò)展后不僅包含鍵列，還包含非鍵列。計(jì)算列上的索引從一個(gè)或多個(gè)其他列的值或某些確定的輸入值派生的列上的索引。篩選一種經(jīng)過(guò)優(yōu)化的非聚集索引，尤其適用于涵蓋從定義完善的數(shù)據(jù)子集中選擇數(shù)據(jù)的查詢。篩選索引使用篩選謂詞對(duì)表中的部分行進(jìn)行索引。與全表索引相比，設(shè)計(jì)良好的篩選索引可以提高查詢性能、減少索引維護(hù)開(kāi)銷并可降低索引存儲(chǔ)開(kāi)銷?？臻g一種經(jīng)過(guò)優(yōu)化的非聚集索引，尤其適用于涵蓋從定義完善的數(shù)據(jù)子集中選擇數(shù)據(jù)的查詢。篩選索引使用篩選謂詞對(duì)表中的部分行進(jìn)行索引。與全表索引相比，設(shè)計(jì)良好的篩選索引可以提高查詢性能、減少索引維護(hù)開(kāi)銷并可降低索引存儲(chǔ)開(kāi)銷。XMLxml數(shù)據(jù)類型列中XML二進(jìn)制大型對(duì)象（BLOB）的已拆分持久表示形式。全文一種特殊類型的基于標(biāo)記的功能性索引，由SQLServer全文引擎生成和維護(hù)。用于幫助在字符串?dāng)?shù)據(jù)中搜索復(fù)雜的詞。9.3.1索引的分類在SQLServer2012中，除了表可以創(chuàng)建索引，視圖也可以創(chuàng)建索引。根據(jù)視圖創(chuàng)建的索引稱為視圖索引。雖然SQLServer2012中提供了許多種索引類型，但通?？梢院?jiǎn)單的將索引根據(jù)其索引鍵值是否唯一，分為唯一索引和不唯一索引。根據(jù)索引列個(gè)數(shù)，可以分為單列索引和組合索引或符合索引。而最常見(jiàn)的索引分類，是按索引的組織方式分類，分為聚集索引和非聚集索引。9.3.1索引的分類1.聚集索引聚集索引基于聚集索引鍵按順序排序和存儲(chǔ)表或視圖中的數(shù)據(jù)行。聚集索引按B樹(shù)索引結(jié)構(gòu)實(shí)現(xiàn)，B樹(shù)索引結(jié)構(gòu)支持基于聚集索引鍵值對(duì)行進(jìn)行快速檢索。在聚集索引中，表中各行的物理順序與索引鍵值的邏輯順序相同。每個(gè)表只能有一個(gè)聚集索引，因?yàn)閿?shù)據(jù)行本身只能按一個(gè)順序排序。只有當(dāng)表包含聚集索引時(shí)，表中的數(shù)據(jù)行才按排序順序存儲(chǔ)。如果表具有聚集索引，則該表稱為聚集表。如果表沒(méi)有聚集索引，則其數(shù)據(jù)行存儲(chǔ)在一個(gè)稱為堆的無(wú)序結(jié)構(gòu)中。以Student表為例，如果經(jīng)常按照Name列查詢記錄，則可以在Name列上創(chuàng)建聚集索引。9.3.1索引的分類2.非聚集索引因?yàn)橐粋€(gè)表中只能有一個(gè)聚集索引，但是由于實(shí)際需要，要在一個(gè)表建立多個(gè)索引，則可以創(chuàng)建非聚集索引。非聚集索引具有獨(dú)立于數(shù)據(jù)行的結(jié)構(gòu)。非聚集索引包含非聚集索引鍵值，并且每個(gè)鍵值項(xiàng)都有指向包含該鍵值的數(shù)據(jù)行的指針。從非聚集索引中的索引行指向數(shù)據(jù)行的指針?lè)Q為行定位器。行定位器的結(jié)構(gòu)取決于數(shù)據(jù)頁(yè)是存儲(chǔ)在堆中還是聚集表中。對(duì)于堆，行定位器是指向行的指針。對(duì)于聚集表，行定位器是聚集索引鍵。聚集索引和非聚集索引都可以是唯一索引的。每當(dāng)修改了表數(shù)據(jù)后，都會(huì)自動(dòng)維護(hù)索引。如果對(duì)表列定義了主鍵約束（即PRIMARYKEY約束）和唯一約束（即UNIQUE約束）時(shí)，會(huì)自動(dòng)創(chuàng)建索引。如果是主鍵約束，系統(tǒng)默認(rèn)是聚集、唯一索引。9.3.2創(chuàng)建索引創(chuàng)建索引時(shí)，首先必須考慮一些設(shè)計(jì)準(zhǔn)則。1.設(shè)計(jì)索引時(shí)應(yīng)考慮的準(zhǔn)則（1）創(chuàng)建索引之前應(yīng)考慮的準(zhǔn)則在創(chuàng)建索引之前應(yīng)考慮如下準(zhǔn)則：1）了解數(shù)據(jù)庫(kù)本身的特征。2）了解最常用的查詢的特征。3）了解查詢中使用的列的特征。4）確定哪些索引選項(xiàng)可在創(chuàng)建或維護(hù)索引時(shí)提高性能。（2）設(shè)計(jì)索引時(shí)應(yīng)考慮的數(shù)據(jù)庫(kù)準(zhǔn)則設(shè)計(jì)索引時(shí)，還應(yīng)考慮以下數(shù)據(jù)庫(kù)準(zhǔn)則：1）一個(gè)表如果建有大量索引，反而會(huì)影響INSERT、UPDATE和DELETE語(yǔ)句的性能，因?yàn)樵诒碇械臄?shù)據(jù)更改時(shí)，所有索引都必須進(jìn)行適當(dāng)?shù)恼{(diào)整。2）避免對(duì)經(jīng)常更新的表進(jìn)行過(guò)多的索引，并且索引應(yīng)保持較窄，就是說(shuō)，列要盡可能少。3）使用多個(gè)索引可以提高更新少而數(shù)據(jù)量大的查詢的性能。大量索引可以提高不修改數(shù)據(jù)的查詢（例如SELECT語(yǔ)句）的性能，因?yàn)椴樵儍?yōu)化器有更多的索引可供選擇，從而可以確定最快的訪問(wèn)方法。4）對(duì)小表進(jìn)行索引可能不會(huì)產(chǎn)生優(yōu)化效果，因?yàn)椴樵儍?yōu)化器在遍歷用于搜索數(shù)據(jù)的索引時(shí)，花費(fèi)的時(shí)間可能比執(zhí)行簡(jiǎn)單的表掃描還長(zhǎng)。因此，小表的索引可能從來(lái)不用，但仍必須在表中的數(shù)據(jù)更改時(shí)進(jìn)行維護(hù)。5）視圖包含聚集函數(shù)、連接或聚集函數(shù)和連接的組合時(shí)，視圖的索引可以顯著地提升性能。9.3.2創(chuàng)建索引2.創(chuàng)建索引（1）管理工具界面方式創(chuàng)建在SQLServerManagementStudio中，用戶在表設(shè)計(jì)器中打開(kāi)Student表。鼠標(biāo)右鍵單擊“索引/鍵”選項(xiàng)，進(jìn)入“索引/鍵”對(duì)話框。由于Student表已經(jīng)將StudentID列設(shè)置為主鍵，所以已經(jīng)存在了一個(gè)以主鍵為索引鍵的唯一索引。9.3.2創(chuàng)建索引如果需要根據(jù)Name列創(chuàng)建索引，選擇“添加”按鈕，添加一個(gè)索引/鍵對(duì)象。選擇“類型”選項(xiàng)設(shè)置為“索引”，選擇“列”選項(xiàng)設(shè)置Name列，選擇“是唯一的”選項(xiàng)設(shè)置為“是”。關(guān)閉并保存退出即可。用戶在Student表的“索引”對(duì)象中可以查看索引。9.3.2創(chuàng)建索引用戶也可以使用向?qū)?chuàng)建索引。這是一種最完整的創(chuàng)建方式。例如，在Book表中，根據(jù)BookName列創(chuàng)建索引。右鍵單擊“索引”選項(xiàng)，選擇右鍵菜單的“新建索引”的“非聚集索引”選項(xiàng)。不能選擇“聚集索引”選項(xiàng)的原因是Book表設(shè)置有主鍵，系統(tǒng)已經(jīng)默認(rèn)創(chuàng)建了一個(gè)聚集索引，而且一個(gè)表只能創(chuàng)建一個(gè)聚集索引。進(jìn)入“新建索引”對(duì)話框，新建索引。在“新建索引”對(duì)話框中，可以通過(guò)選擇不同的選項(xiàng)來(lái)設(shè)置索引。9.3.2創(chuàng)建索引在“常規(guī)”頁(yè)中，在“索引名稱”中輸入新建索引名稱“BookNameIndex”，在“索引類型”中默認(rèn)為“非聚集”。選擇“添加”按鈕，將BookName列添加到“索引鍵列”中。其它頁(yè)還可以設(shè)置索引的其它屬性。關(guān)閉保存退出即可。用戶在Book表的“索引”對(duì)象中可以查看索引。9.3.2創(chuàng)建索引（2）命令行方式創(chuàng)建在SQLServer2012中，T-SQL提供了索引創(chuàng)建語(yǔ)句CREATEINDEX。其語(yǔ)法格式如下：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_nameON<object>(column[ASC|DESC][,...n])[INCLUDE(column_name[,...n])][WHERE<filter_predicate>][WITH(<relational_index_option>[,...n])][ON{partition_scheme_name(column_name)|filegroup_name|default}][FILESTREAM_ON{filestream_filegroup_name|partition_scheme_name|"NULL"}]CREATEINDEX語(yǔ)句語(yǔ)法說(shuō)明：1）index_name是所創(chuàng)建的索引名。2）UNIQUE、CLUSTERED、NONCLUSTERED是索引類型。其中，UNIQUE表示唯一索引，CLUSTERED表示聚集索引，NONCLUSTERED表示非聚集索引，而且必須先創(chuàng)建UNIQUE索引，然后才能創(chuàng)建NONCLUSTERED索引。3）ONcolumn用于指定創(chuàng)建索引的列。4）ASC表示索引按升序創(chuàng)建，DESC表示索引按降序創(chuàng)建。9.3.2創(chuàng)建索引【例9-12】已經(jīng)新建一個(gè)Student2表，列和Student表相同，沒(méi)有任何索引設(shè)置。根據(jù)StudentName列創(chuàng)建一個(gè)名為NameIndex的聚集索引。USECOLLEGEGOCREATEUNIQUECLUSTEREDINDEXNameIndexONStudent2(Name)9.3.3使用系統(tǒng)存儲(chǔ)過(guò)程和系統(tǒng)視圖查看索引索引創(chuàng)建完畢，不但可以在“對(duì)象資源管理器”中查看索引，還可以使用系統(tǒng)存儲(chǔ)過(guò)程和系統(tǒng)視圖查看索引。1.使用sp_helpindex系統(tǒng)存儲(chǔ)過(guò)程sp_helpindex是SQLServer2012系統(tǒng)存儲(chǔ)過(guò)程，可以查看指定表或視圖的索引信息。其語(yǔ)法格式如下：sp_helpindextable_name|view_name【例9-13】執(zhí)行sp_helpindex系統(tǒng)存儲(chǔ)過(guò)程查看Student表的索引信息。USECOLLEGEGOsp_helpindexStudentGo結(jié)果中顯示了Student表的索引信息，包括索引名稱、索引列名稱等。9.3.3使用系統(tǒng)存儲(chǔ)過(guò)程和系統(tǒng)視圖查看索引2.從系統(tǒng)視圖sys.indexes中查詢索引sys.indexes是SQLServer2012系統(tǒng)視圖，它保存了指定數(shù)據(jù)庫(kù)中的所有表或視圖等對(duì)象的索引信息?！纠?-14】使用連接方式，將系統(tǒng)視圖sys.indexes與系統(tǒng)視圖sys.objects相關(guān)聯(lián)，獲得更詳細(xì)的結(jié)果集。USECOLLEGEGOSELECTAS'表名',AS'索引名',i.type_descAS'類型描述'FROMsys.objectsoJOINsys.indexesiONo.object_id=i.object_idGO結(jié)果中顯示了COLLEGE數(shù)據(jù)庫(kù)中所有的索引信息，包括表名、索引名稱、索引類型。9.3.4修改索引如果索引設(shè)置不合適可以修改。1.管理工具界面方式修改索引可以在表結(jié)構(gòu)設(shè)計(jì)器中修改，也可以選擇右鍵菜單選項(xiàng)對(duì)索引進(jìn)行刪除、重命名、禁用、重新生成、重新組織等。2.命令行方式修改在SQLServer2012中，T-SQL提供了索引修改語(yǔ)句ALTERINDEX。其語(yǔ)法格式如下：ALTERINDEX{index_name|ALL}ON<object>{REBUILD|DISABLE|REORGANIZE}ALTERINDEX語(yǔ)句語(yǔ)法說(shuō)明：1）REBUILD子句指定重新生成索引。2）DISABLE子句指禁用索引。3）REORGANIZE子句指重新組織索引。【例9-15】

禁用【例9-12】創(chuàng)建的索引。USECOLLEGEGOALTERINDEXNameIndexONStudent2DISABLE【例9-16】重新啟用該索引。USECOLLEGEGOALTERINDEXNameIndexONStudent2REBUILD9.3.5刪除索引如果索引不需要了，可以刪除。1.管理工具界面方式刪除索引可以在表結(jié)構(gòu)設(shè)計(jì)器中刪除，也可以選擇右鍵菜單選項(xiàng)對(duì)索引進(jìn)行刪除。2.命令行方式刪除在SQLServer2012中，T-SQL提供了索引修改語(yǔ)句ALTERINDEX。其語(yǔ)法格式如下：DROPINDEXindex_name9.3.6其它類型索引除了以上常用的索引之外，SQLServer2012其他的索引，例如全文索引、空間索引、篩選索引、XML數(shù)據(jù)類型列索引等。除了全文索引之外，其他類型索引，限于本書(shū)篇幅有限，不再詳細(xì)介紹。1.全文索引全文索引是一種特殊類型的基于標(biāo)記的功能性索引，它是由SQLServer全文引擎生成和維護(hù)的。生成全文索引的過(guò)程不同于生成其他類型的索引。全文引擎并非基于特定行中存儲(chǔ)的值來(lái)構(gòu)造B樹(shù)結(jié)構(gòu)，而是基于要編制索引的文本中的各個(gè)標(biāo)記來(lái)生成倒排、堆積且壓縮的索引結(jié)構(gòu)。從SQLServer2012開(kāi)始，全文索引與數(shù)據(jù)庫(kù)引擎集成在一起，而不是像SQLServer早期版本那樣位于文件系統(tǒng)中。對(duì)于新數(shù)據(jù)庫(kù)，全文目錄現(xiàn)在為不屬于任何文件組的虛擬對(duì)象；它僅是一個(gè)表示一組全文索引的邏輯概念。9.3.6其它類型索引（1）定義全文索引全文索引可以通過(guò)全文索引向?qū)Фx。例如給Course表定義全文索引。單擊Course表，選擇右鍵菜單“全文索引”選項(xiàng)的“定義全文索引”子選項(xiàng)。進(jìn)入全文索引向?qū)С跏冀缑妗?.3.6其它類型索引選擇“下一步”按鈕，進(jìn)入下一界面，選擇索引，而且必須選擇唯一索引。選擇“下一步”按鈕，進(jìn)入下一界面，選擇表列。9.3.6其它類型索引選擇“下一步”按鈕，進(jìn)入下一界面，選擇更改跟蹤模式。其中，“自動(dòng)”表示當(dāng)基礎(chǔ)數(shù)據(jù)發(fā)生變化，全文索引將自動(dòng)更新。“手動(dòng)”表示不希望基礎(chǔ)數(shù)據(jù)發(fā)生變化全文索引自動(dòng)更新，對(duì)基礎(chǔ)數(shù)據(jù)的更改將保留下來(lái)，不過(guò)如果要將更改應(yīng)用到全文索引，必須手動(dòng)啟動(dòng)或安排此進(jìn)程?！安桓櫢摹北硎静幌Ｍ褂没A(chǔ)數(shù)據(jù)的更改對(duì)全文索引進(jìn)行更新。選擇“下一步”按鈕，進(jìn)入下一界面，選擇全文目錄。如果還沒(méi)有全文目錄，可以新建目錄。9.3.6其它類型索引選擇“下一步”按鈕，進(jìn)入下一界面，定義填充計(jì)劃。在此可以創(chuàng)建全文索引和全文目錄的填充計(jì)劃，也可以在下一步后，在創(chuàng)建完全文索引后再創(chuàng)建填充計(jì)劃。選擇“下一步”按鈕，進(jìn)入下一界面，全文索引向?qū)дf(shuō)明。最后選擇“完成”按鈕，完成全文索引定義。9.3.6其它類型索引（2）查看和修改全文向?qū)乃饕x完畢，可以查看或修改全文索引。選擇右鍵菜單“全文索引”選項(xiàng)的“屬性”子選項(xiàng)，查看或修改全文索引的設(shè)置。用戶可以在全文索引屬性對(duì)話框中查看、修改等操作。9.3.6其它類型索引（3）啟用、禁用和刪除全文索引全文索引定義完畢，不會(huì)立即自動(dòng)啟用，需要手工啟動(dòng)。右鍵單擊Course表，選擇“全文索引”選項(xiàng)的“啟用全文索引”子選項(xiàng)、“禁用全文索引”子選項(xiàng)、“刪除全文索引”子選項(xiàng)，可以啟用全文索引、禁用全文索引和刪除全文索引。9.3.6其它類型索引（4）填充全文索引填充全文索引實(shí)際就是更新全文索引，其目的是讓全文索引能夠反映最新的表數(shù)據(jù)。SQLServer2012支持3種類型的填充：完全填充、基于更改跟蹤的自動(dòng)或手動(dòng)填充，以及基于時(shí)間戳的增量式填充。完全填充方式發(fā)生在首次填充全文目錄或全文索引時(shí)。啟用全文索引即進(jìn)行了第一次的完全填充。以后就可以使用基于更改跟蹤的自動(dòng)或手動(dòng)填充和以及基于時(shí)間戳的增量式填充?；诟母櫟淖詣?dòng)或手動(dòng)填充也是選擇快捷菜單選項(xiàng)設(shè)置?；跁r(shí)間戳的增量式填充，在“全文索引屬性”對(duì)話框中，選擇“計(jì)劃”選項(xiàng)卡。新建一個(gè)全文索引表計(jì)劃。設(shè)置計(jì)劃的名稱、執(zhí)行一次的日期時(shí)間等。設(shè)置完成，可以修改填充類型。（5）使用全文索引設(shè)置完全文索引并填充完畢之后，就可以通過(guò)全文搜索來(lái)查詢數(shù)據(jù)了。使用全文搜索來(lái)查詢數(shù)據(jù)所用到的T-SQL語(yǔ)句也是SELECT語(yǔ)句，只是在設(shè)置查詢條件時(shí)和前面所說(shuō)過(guò)的SELECT語(yǔ)句的查詢條件設(shè)置有些不同。在T-SQL語(yǔ)言中，可以在SELECT語(yǔ)句的WHERE子句里設(shè)置全文搜索的查詢條件，也可以在FROM子句里設(shè)置查詢條件，此時(shí)將返回結(jié)果作為FROM子句中的表格來(lái)使用。9.3.6其它類型索引2.空間索引SQLServer2012及更高版本支持空間數(shù)據(jù)。這包括對(duì)平面空間數(shù)據(jù)類型geometry的支持，該數(shù)據(jù)類型支持歐幾里得坐標(biāo)系統(tǒng)中的幾何數(shù)據(jù)（點(diǎn)、線和多邊形）。3.篩選索引篩選索引是一種經(jīng)過(guò)優(yōu)化的非聚集索引，尤其適用于涵蓋從定義完善的數(shù)據(jù)子集中選擇數(shù)據(jù)的查詢。篩選索引使用篩選謂詞對(duì)表中的部分行進(jìn)行索引。與全表索引相比，設(shè)計(jì)良好的篩選索引可以提高查詢性能、減少索引維護(hù)開(kāi)銷并可降低索引存儲(chǔ)開(kāi)銷。4.XML數(shù)據(jù)類型列索引可以對(duì)xml數(shù)據(jù)類型列創(chuàng)建XML索引。它們對(duì)列中XML實(shí)例的所有標(biāo)記、值和路徑進(jìn)行索引，從而提高查詢性能。XML索引分為下列類別：主XML索引輔助XML索引xml類型列的第一個(gè)索引必須是主XML索引。使用主XML索引時(shí)，支持下列類型的輔助索引：PATH、VALUE和PROPERTY。根據(jù)查詢類型的不同，這些輔助索引可能有助于改善查詢性能。9.3.7優(yōu)化索引1.索引性能分析SQLServer提供了多種分析索引和查詢性能的方法。常用的有SHOWPLAN和STATISTICSIO兩種命令。（1）SHOWPLAN通過(guò)在查詢語(yǔ)句中設(shè)置SHOWPLAN選項(xiàng)，用戶可以選擇是否讓SQLServer顯示查詢計(jì)劃。在查詢計(jì)劃中，系統(tǒng)將顯示SQLServer在執(zhí)行查詢的過(guò)程中連接表時(shí)所采用的每個(gè)步驟以及選擇哪個(gè)索引，從而可以幫助用戶分析創(chuàng)建的索引是否被系統(tǒng)使用。設(shè)置顯示查詢計(jì)劃的語(yǔ)句有：SETSHOWPLAN_XML|SHOWPLAN_TEXT|SHOWPLAN_ALLON本句執(zhí)行后，如果是SHOWPLAN_XML，SQLServer不執(zhí)行SQL語(yǔ)句，而返回如何在正確的XML文檔中執(zhí)行語(yǔ)句的執(zhí)行計(jì)劃信息。如果是SHOWPLAN_TEXT，SQLServer以文本格式返回每個(gè)查詢的執(zhí)行計(jì)劃信息。如果是SHOWPLAN_ALL，輸出比SHOWPLAN_TEXT更詳細(xì)的信息。設(shè)置完并執(zhí)行SQL后，還要關(guān)閉該設(shè)置。9.3.7優(yōu)化索引【例9-17】使用SHOWPLAN選項(xiàng)查詢，并顯示查詢處理過(guò)程。USECOLLEGEGOSETSHOWPLAN_XMLONGOSELECTName,Sex,BirthdayFROMStudentGOSETSHOWPLAN_XMLOFFGO查詢結(jié)果顯示的是一行鏈接提示信息。查看鏈接，系統(tǒng)顯示顯示本次查詢處理過(guò)程的情況。9.3.7優(yōu)化索引（2）STATISTICSIO通過(guò)在查詢語(yǔ)句中設(shè)置STATISTICSIO選項(xiàng)，用戶可以使SQLServer顯示數(shù)據(jù)檢索語(yǔ)句執(zhí)行后生成的有關(guān)磁盤活動(dòng)量的文本信息?！纠?-18】使用STATISTICSIO選項(xiàng)查詢，并顯示查詢處理過(guò)程。USECOLLEGEGOSETSTATISTICSIOONGOSELECTCourseName,CreditFROMCourseGOSETSTATISTICSIOOFFGO查看查詢結(jié)果的消息，顯示本次查詢的磁盤I/O的信息。9.3.7優(yōu)化索引2.查看索引碎片無(wú)論何時(shí)對(duì)基礎(chǔ)數(shù)據(jù)執(zhí)行插入、更新或刪除操作