索引和視圖第1頁，共161頁。（優(yōu)選）索引和視圖第2頁，共161頁。本章的學習目標索引的優(yōu)點和缺點堆的結構特點聚集索引和非聚集索引的特點索引的類型使用CREATEINDEX語句創(chuàng)建索引的方式3第3頁，共161頁。概述索引是一種與表或視圖關聯(lián)的物理結構，可以用來加快從表或視圖中檢索數(shù)據(jù)行的速度。創(chuàng)建索引的目的：創(chuàng)建唯一性索引，可以保證每一行數(shù)據(jù)的唯一性可以大大加快數(shù)據(jù)的檢索速度可以加速表和表之間的連接使用ORDERBY和GROUPBY子句進行數(shù)據(jù)檢索時，索引可以顯著減少查詢中分組和排序的時間4第4頁，共161頁。索引的類型和特點SQLServer2005系統(tǒng)中有兩種基本的索引類型：聚集索引和非聚集索引。其他類型包括唯一性索引、包含性索引、索引視圖、全文索引及XML索引等。5第5頁，共161頁。數(shù)據(jù)的存儲SQLServer中的數(shù)據(jù)文件會以8KB大小分頁每一頁可包括數(shù)據(jù)、索引、或者其他SQLServer需要為其維護數(shù)據(jù)文件的數(shù)據(jù)類型索引的數(shù)據(jù)也像表一樣放在數(shù)據(jù)頁上頁是SQLServer讀、寫數(shù)據(jù)文件的單元創(chuàng)建沒有任何索引的表并向其中插入數(shù)據(jù)的時候，數(shù)據(jù)庫搜索未被使用的頁來存儲這些數(shù)據(jù)。系統(tǒng)為每一個表設立一個或多個IAM(索引分配映射)頁指向保存表數(shù)據(jù)的頁。6第6頁，共161頁。表的IAM頁的結構7第7頁，共161頁。無索引的表的數(shù)據(jù)存儲未建立索引的表的數(shù)據(jù)以無序的方式存儲在頁上，只通過IAM頁聯(lián)系在一起，這類表被稱作堆或堆集SQLServer通過閱讀這個表的IAM頁并且通過掃描IAM頁指向的所有頁來訪問一個堆的數(shù)據(jù)。這種操作稱為表掃描。如果一個查詢要搜索一個特定的行，堆的表掃描必須讀取表中的所有行來找到它表中數(shù)據(jù)很多時，這是一種非常耗時的操作21,2,6,13,25,7,3,46,62,74,112……在上面無序的數(shù)據(jù)中找到148第8頁，共161頁。有序數(shù)據(jù)集上數(shù)據(jù)的查找1，3，4，12，33，45，52，53，61……如果數(shù)據(jù)的存儲是有序的，按照次序在表中查找，找到后可確保剩下的數(shù)據(jù)中沒有要求的數(shù)據(jù)更快速的方法:折半查找法在有序集的中位數(shù)上比較，如果該值比要查找的值小，則一定在后半數(shù)據(jù)中與后半數(shù)據(jù)的中位數(shù)比較，如果小于中位數(shù)，則在后半數(shù)據(jù)的前半段中每次可以排除當前數(shù)據(jù)集的一半9第9頁，共161頁。索引為了提高數(shù)據(jù)訪問的性能，可為列定義索引，定義索引的列稱作索引鍵列索引的字典定義如下：通常以一些指定數(shù)據(jù)(如作者、主題或者關鍵字)的字母順序排列的列表(作為著作的主要部分的目錄信息或者引用)在列上建立的索引與一本書的索引類似。它包括在此列上排序好的值以及指向可以找到這些實際數(shù)據(jù)行所在頁的指針（地址）10第10頁，共161頁。11第11頁，共161頁。索引的邏輯結構：平衡樹(B-樹)索引在數(shù)據(jù)庫中采用平衡樹(B樹)來存儲平衡樹在根節(jié)點處開始對于少量數(shù)據(jù)，根節(jié)點可直接指向數(shù)據(jù)的實際位置為了在索引中快速地找到值，樹通過使用<(小于)和>(大于)的比較操作構建。12第12頁，共161頁。在根節(jié)點開始瀏覽記錄，直到找到小于正在查找值的數(shù)值的最后一個值。然后獲得指向該節(jié)點的一個指針，并且瀏覽它，直到找到想要的行。13第13頁，共161頁。B樹當根節(jié)點比較多時，會創(chuàng)建中間層，根節(jié)點指向中間的非葉層次的節(jié)點。非葉層次的節(jié)點可以指向其他非葉層次的節(jié)點，或者指向葉層次的節(jié)點葉層次的節(jié)點是在其中獲得實際物理數(shù)據(jù)的引用的節(jié)點14第14頁，共161頁。15第15頁，共161頁。頁面拆分B-樹中的B代表平衡(Balanced)。樹中的分支，大約一半的數(shù)據(jù)是在任意一邊的當添加數(shù)據(jù)到樹上時，節(jié)點將要最終填滿，并且將需要拆分——稱為頁面拆分頁面拆分時，需移動數(shù)據(jù)以保持內(nèi)容上的平衡16第16頁，共161頁。在B樹上插入記錄，拆分過程增加了大量的系統(tǒng)開銷沒有簡單地插入一頁，而是：創(chuàng)建新的頁面將行從現(xiàn)有的頁面移動到新的頁面上添加新行到一個頁面上在父節(jié)點內(nèi)添加另一個記錄項17第17頁，共161頁。維護B樹所帶來的系統(tǒng)開銷由于頁面拆分創(chuàng)建新的頁面時，需要在父節(jié)點中建立另一個記錄項在父節(jié)點內(nèi)的這個記錄項在該層次也具有導致頁面拆分的潛在可能這種可能性至能影響根節(jié)點在一個樹的排列中，有級聯(lián)動作內(nèi)容的可能性頁面拆分對系統(tǒng)性能產(chǎn)生非常消極的影響，在服務器上表現(xiàn)出來可能會暫停響應幾秒18第18頁，共161頁。聚集索引聚集索引：數(shù)據(jù)表的物理順序與索引順序相同非聚集索引：數(shù)據(jù)表的物理順序與索引順序不相同聚集索引將索引列在表內(nèi)排序并存儲對應的數(shù)據(jù)記錄，使得數(shù)據(jù)表物理順序與索引順序一致一個表只能有一個聚集索引創(chuàng)建或修改聚集索引可能要花很長時間，因為執(zhí)行這兩個操作時要在磁盤上對表的行進行重組。19第19頁，共161頁。聚集索引聚集索引的B樹結構中B樹結構中的每一頁稱為一個索引節(jié)點索引的最低級節(jié)點是葉級節(jié)點索引級別的數(shù)量取決于行的數(shù)量以及索引鍵的大小頁的大小是8KB,SQLServer可以通過一個索引頁指向幾百個數(shù)據(jù)頁即使表包括數(shù)百萬行的數(shù)據(jù)，索引一般也沒有很多級別20第20頁，共161頁。聚集索引聚集索引的葉層次是實際的數(shù)據(jù)數(shù)據(jù)被重新排序，從而以和索引排序標準聲明的相同物理順序存儲一旦到達索引的葉層次，就完成了工作——即到達了數(shù)據(jù)。任何新的記錄根據(jù)它的正確物理順序插入到聚集索引21第21頁，共161頁。新頁面的創(chuàng)建方法根據(jù)需要在何處插入記錄而變化新記錄插入索引結構中間時發(fā)生正常的頁面拆分來自舊頁面的后一半記錄被移動到新頁面新的記錄插入到新的頁面或舊的頁面若新記錄邏輯上位于索引結構末端，則創(chuàng)建一個新的頁面，只有新的記錄添加到新的頁面22第22頁，共161頁。聚集索引的結構示意圖23第23頁，共161頁。聚集索引的B樹可視化效果24第24頁，共161頁。聚集索引聚集索引葉級頁面存放實際數(shù)據(jù)而不包括索引鍵和指針，這種設計具有兩個優(yōu)點：SQLServer不需要依據(jù)指針來訪問數(shù)據(jù)，數(shù)據(jù)直接存儲在索引中數(shù)據(jù)依據(jù)索引鍵排序。若SQLServer需要依據(jù)索引鍵排序數(shù)據(jù)，不必再執(zhí)行排序操作，因為數(shù)據(jù)已經(jīng)排好序了。25第25頁，共161頁。非聚集索引非聚集索引頁中數(shù)據(jù)行不按非聚集索引鍵的順序排序和存儲SQLServer按B樹組織非聚集索引非聚集索引的葉級是由索引頁而不是由數(shù)據(jù)頁組成，索引行由非聚集鍵值和行定位符組成行定位符指向聚集索引或堆中包含該鍵值的數(shù)據(jù)行，以行ID(RID)的形式出現(xiàn)，RID指定了特定行的盤區(qū)、頁面以及行偏移若表沒有定義聚集索引，則新記錄插入表的末尾26第26頁，共161頁。非聚集索引在非聚集索引中查找連續(xù)的多個行最糟糕的情況：從非聚集索引中獲得的每一行都要求一個額外的不連續(xù)磁盤I/O才能檢索行數(shù)據(jù)最好的情況：所需要的行有許多都位于相同的數(shù)據(jù)頁，因此在提取每個數(shù)據(jù)頁時可檢索多行。對于聚集索引，索引的葉級節(jié)點是表的實際數(shù)據(jù)行，檢索表數(shù)據(jù)時不需要指針跳動27第27頁，共161頁。非聚集索引的可視化效果28第28頁，共161頁。非聚集索引使用兩種索引的相同點和不同點以相同的根節(jié)點開始，遍歷樹，直到到達索引的葉層次。聚集索引的方式，在此處停止非聚集索引的方式，在此時獲得來自葉層次頁面的行ID，再定位到行才可直接獲得實際的數(shù)據(jù)29第29頁，共161頁。漢語字典的正文是一個聚集索引查“安”字，按照拼音排序漢字的字典是以英文字母“a”開頭并以“z”結尾的，“安”字就自然地排在字典的前部。如果翻完了所有以“a”開頭的部分仍然找不到這個字，說明該字典中無此字查“張”字，將字典翻到最后部分，因為“張”的拼音是“zhang”。字典的正文部分本身就是一個目錄，不需要再去查其他目錄來查找內(nèi)容。30第30頁，共161頁。部首查字表是非聚集索引檢字表中字的排序并不是正文的排序“張”字，672頁檢字表中“張”的上面是“馳”字，63頁“張”的下面是“弩”字，390頁連續(xù)的“馳、張、弩”三字是它們在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。查字需要兩個過程，先找到目錄中的結果，再翻到所需頁碼這種目錄純粹是目錄，正文純粹是正文的排序方式為非聚集索引31第31頁，共161頁。索引的副作用第一，創(chuàng)建索引和維護索引要耗費時間。第二，索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個索引還要占一定的物理空間。如果建立聚集索引，需要的空間會更大。第三，當對表中的數(shù)據(jù)進行增加、刪除和修改的時候，索引也要動態(tài)的維護，這樣就降低了數(shù)據(jù)的維護速度。32第32頁，共161頁。其他類型的索引唯一性索引、包含性列索引、索引視圖、全文索引和XML索引。唯一索引：不允許兩行具有相同的索引值如果現(xiàn)有數(shù)據(jù)中存在重復的鍵值，則不能創(chuàng)建唯一索引當新數(shù)據(jù)將使表中的鍵值重復時，數(shù)據(jù)庫拒絕接受此數(shù)據(jù)例如，如果在employee表中的職員姓氏(lname)列上創(chuàng)建了唯一索引，則所有職員不能同姓。33第33頁，共161頁。唯一性如果聚集索引不是唯一的，那將怎樣？聚集索引如何用于唯一地識別一行？SQLServer迫使任何聚集索引為唯一的，即使沒有以這樣的方式定義。SQLServer將在內(nèi)部添加一個后綴到鍵上，以保證行具有唯一的標識符。34第34頁，共161頁。覆蓋索引(包含性索引)覆蓋索引:非聚集復合索引的一種形式，包括在查詢里的Select,Join和Where子句用到的所有列索引包含了查詢正在查找的數(shù)據(jù)覆蓋索引可以節(jié)省大量的I/O，因此可極大地改善查詢的性能。如果覆蓋索引對于SQLServer上經(jīng)常運行的查詢或查詢組極其有利，那么創(chuàng)建覆蓋索引是值得的。覆蓋索引的創(chuàng)建和維護要付出很大代價，請確保查詢優(yōu)化器使用所創(chuàng)建的覆蓋索引使用CREATEINDEX中的INCLUDE選項35第35頁，共161頁。確定是什么導致了好的索引索引是數(shù)據(jù)庫中的熱點問題，并不是定義的索引越多越好，定義不好的索引可能還不如不定義索引好的索引有什么特點使用“低維護”列使用主鍵和外鍵能夠找到指定的記錄使用覆蓋索引查找信息范圍保持數(shù)據(jù)的排序36第36頁，共161頁。低維護列雖然在讀數(shù)據(jù)時索引可提高性能，但在修改數(shù)據(jù)時，它們實際上代價很高每一次對數(shù)據(jù)進行修改時，任何關于該數(shù)據(jù)的索引也需要被更新插入新行時，必須將新的記錄項放入表上的每一個索引中更新一行時，將該操作作為刪除和插入進行處理，索引必須被更新當刪除記錄時，必須也更新所有的索引——而不只是數(shù)據(jù)37第37頁，共161頁。低維護列平衡樹有多個層次對葉層次進行修改時，有可能發(fā)生頁面劃分，而且一個或者多個非葉層次的頁面也必須被修改有時不創(chuàng)建額外的索引是需要做的事情。有時候，要做的最好的事情是基于對系統(tǒng)而言至關重要的事務選擇索引，并且討論正在使用的表：事務的代碼在其中是否有WHERE子句?它使用了什么列?需要進行排序么?38第38頁，共161頁。主鍵和外鍵如果表中有一個作為外鍵的列，則查詢將得益于在該列上有一個索引。外鍵常常是與它們引用的表連接的目標。如果兩個表中的相關列上存在索引，對兩個行的定位會非?？臁?9第39頁，共161頁。找到指定記錄索引的目的是在查詢中讓列允許SQLServer快速標識出滿足要求的行。要求由SELECT語句中的WHERE子句表達SELECT*FROMXSWHERE專業(yè)名='計算機'ORDERBY出生時間在最常用的查詢(或要求有較高性能的查詢)中，用于WHERE子句中的列最好為其創(chuàng)建索引40第40頁，共161頁。查找信息范圍若需要查找一定范圍的記錄，創(chuàng)建索引將會提高操作性能索引建立記錄按照某個或若干個字段按照給定規(guī)則的排序結構41第41頁，共161頁。保持數(shù)據(jù)的排序如果數(shù)據(jù)已經(jīng)排序，則可極大地減少在select語句中排序的開銷聚集索引按照數(shù)據(jù)的排序安排數(shù)據(jù)的物理存儲位置，當在中間位置插入數(shù)據(jù)時，由于數(shù)據(jù)頁被填滿可能會發(fā)生分頁，這會帶來很大的磁盤I/O開銷。為避免頻繁地分頁，SQLServer中在創(chuàng)建索引時可以指定一個填充因子FILLFACTOR42第42頁，共161頁。填充因子FILLFACTOR當SQLServer第一次創(chuàng)建索引時，默認情況下盡可能將其填滿，只剩余兩個記錄的空間。FILLTACTOR設置為在1和100之間的任意值，這個數(shù)字將代表填充頁面的百分比。OLTP系統(tǒng)，需要較低的FILLFACTOROLAP或者其他非常穩(wěn)定的系統(tǒng)，需要盡可能高的FILLFACTOR如果沒有提供值，SQLServer將填充頁面，其中有兩行不滿，并且每頁最少一行43第43頁，共161頁。當今的數(shù)據(jù)處理大致可以分成兩大類：聯(lián)機事務處理OLTP（on-linetransactionprocessing）、聯(lián)機分析處理OLAP（on-lineanalyticalprocessing）OLTP是傳統(tǒng)的關系型數(shù)據(jù)庫的主要應用，主要是基本的、日常的事務處理，例如銀行交易，強調(diào)插入、刪除和修改操作的及時響應。OLAP是數(shù)據(jù)倉庫系統(tǒng)的主要應用，支持復雜的分析操作，側重決策支持，并且提供直觀易懂的查詢結果，強調(diào)復雜查詢的性能。44第44頁，共161頁。填充因子FILLFACTORFILLTACTOR設置為在1和100之間的任意值，這個數(shù)字將代表填充頁面的百分比。填充因子較小時索引的B樹的深度大，查詢較慢，但潛在的分頁可能性小填充因子較大時索引的B樹的深度小，查詢較快，但潛在的分頁可能性大數(shù)據(jù)庫使用一段時間后需要重新維護索引以保持適度的填充因子水平45第45頁，共161頁。確定是什么導致了壞的索引不好的索引或者對數(shù)據(jù)的檢索沒有幫助，甚至會嚴重影響系統(tǒng)的性能從以下幾個角度檢查是不是創(chuàng)建了壞的索引使用了不合適的列選擇了不合適的數(shù)據(jù)包含了過多的列表中包含的記錄過少46第46頁，共161頁。確定是什么導致了壞的索引使用了不合適的列不要為不需進行查詢的列建立索引，這只會帶來開銷，而不會提高查詢性能表中包含的記錄過少 當表中的記錄過少時，雖然也可為其創(chuàng)建索引，但很可能SQL的查詢優(yōu)化器根本不使用它47第47頁，共161頁。選擇了不合適的數(shù)據(jù)非聚集索引中索引鍵列的選擇性(唯一性)很重要如果在只有少量唯一值的大型表上創(chuàng)建非聚集索引，使用非聚集索引將不會節(jié)省數(shù)據(jù)檢索中的I/O。使用這種索引產(chǎn)生的I/O可能比對表進行連續(xù)掃描所產(chǎn)生的I/O多得多。主鍵列上的索引是最優(yōu)的,在索引中查詢的命中率最高適合非聚集索引的列：發(fā)票編號、唯一的客戶編號、社會安全號碼和電話號碼。48第48頁，共161頁。包含了過多的列索引中包含的列越多，插入或修改數(shù)據(jù)時被移動的數(shù)據(jù)就越多如果包含了過多的列，請考慮重新設計索引索引結構中較為常見的一種錯誤是考慮包含所有列的索引將對任何情況都是有幫助的。如果索引的第一列沒有在查詢中的JOIN、ORDERBY，或者WHERE子句中被提及，那么索引將完全地被忽略。49第49頁，共161頁。針對性能對索引進行回顧為數(shù)據(jù)庫項目創(chuàng)建正確索需要考慮許多因素：數(shù)據(jù)庫的數(shù)據(jù)模型表中數(shù)據(jù)的數(shù)量和分布對數(shù)據(jù)庫執(zhí)行哪些查詢查詢發(fā)生的頻率數(shù)據(jù)更新的頻率為了幫助設計索引，SQLServer提供了一個稱為“數(shù)據(jù)庫引擎優(yōu)化顧問”的工具。50第50頁，共161頁。針對性能對索引進行回顧數(shù)據(jù)庫引擎優(yōu)化顧問需要一個工作負荷文件，該文件可以是包含需要優(yōu)化的語句的一個文本文件，也可以是SQLServerProfiler生成的一個跟蹤文件?！皵?shù)據(jù)庫引擎優(yōu)化顧問”會使用SQLServer查詢優(yōu)化器和已有的數(shù)據(jù)庫為數(shù)據(jù)庫物理設計結構提出更改建議，例如創(chuàng)建、更改或刪除各種索引。51第51頁，共161頁。索引小結聚集索引經(jīng)常比非聚集索引更快僅僅在將得到高級別選擇性的列上(行的95%或者更多是唯一的)放置非聚集索引。所有的數(shù)據(jù)操作語言(如：INSERT、UPDATE、DELETE、SELECT)語句可以通過索引獲益，但是插入、刪除和更新(先刪除后插入實現(xiàn))會由于索引而減慢執(zhí)行速度。索引給查詢的查找部分提供幫助，但是任何修改數(shù)據(jù)的行為將有額外的工作要做(以保持除了實際數(shù)據(jù)的索引)。52第52頁，共161頁。索引小結索引占據(jù)空間。僅在索引中的第一列和查詢相關時才使用索引索引的負面影響和它的正面影響一樣多——了解為什么建立索引，以及不建立不需要的索引常規(guī)索引設計指南53第53頁，共161頁。創(chuàng)建索引P131在MicrosoftSQLServer2005系統(tǒng)中，既可以直接創(chuàng)建索引，也可以間接創(chuàng)建索引。當直接創(chuàng)建索引時，可以使用CREATEINDEX語句，也可以使用圖形工具。使用圖形工具創(chuàng)建索引54第54頁，共161頁。直接方法和間接方法直接創(chuàng)建索引：使用命令和工具直接創(chuàng)建索引間接創(chuàng)建索引：通過創(chuàng)建其他對象而附加創(chuàng)建索引在表中定義主鍵約束或唯一性約束的同時也創(chuàng)建了索引使用CREATEINDEX語句或使用創(chuàng)建索引向?qū)韯?chuàng)建索引，這是最基本的索引創(chuàng)建方式55第55頁，共161頁。

使用CREATEINDEX語句P132

CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_name

ON<object>(column[ASC|DESC][,...n])

[INCLUDE(column_name[,...n])]

[WITH(<relational_index_option>[,...n])]56第56頁，共161頁。

使用CREATEINDEX語句P132<relational_index_option>::={PAD_INDEX={ON|OFF}|FILLFACTOR=fillfactor

|SORT_IN_TEMPDB={ON|OFF}

|IGNORE_DUP_KEY={ON|OFF}

|STATISTICS_NORECOMPUTE={ON|OFF}

|DROP_EXISTING={ON|OFF}

|ONLINE={ON|OFF}

|……}57第57頁，共161頁。UNIQUE為表或視圖創(chuàng)建唯一索引唯一索引不允許兩行具有相同的索引鍵值。視圖的聚集索引必須唯一若試圖向行中插入重復的值，SQL會返回一個錯誤信息唯一索引中使用的列應設置為NOTNULL：在創(chuàng)建唯一索引時，會將多個空值視為重復值。58第58頁，共161頁。CLUSTERED創(chuàng)建聚集索引。一個表或視圖只允許同時有一個聚集索引具有聚集索引的視圖稱為索引視圖。必須先為視圖創(chuàng)建唯一聚集索引，然后才能定義其它索引請在創(chuàng)建任何非聚集索引之前創(chuàng)建聚集索引創(chuàng)建聚集索引時重建表上現(xiàn)有的非聚集索引聚集和唯一（[UNIQUE][CLUSTERED）并不是相互包含的59第59頁，共161頁。NONCLUSTERED創(chuàng)建一個指定表的邏輯排序的索引數(shù)據(jù)行的物理排序獨立于索引排序每個表都最多可包含249個非聚集索引默認值為NONCLUSTERED

由于數(shù)據(jù)的實際存儲位置與聚集索引有關，所以當改變聚集索引時，非聚集索引也會變化。60第60頁，共161頁。CREATEINDEX語法Index_name索引的名稱。索引名稱在表或視圖中必須唯一，但在數(shù)據(jù)庫中不必唯一。須符合標識符規(guī)則建議格式：IX_表名_列名column索引所基于的一列或多列。指定兩個或多個列名，可為指定列的組合值創(chuàng)建組合索引。按排序優(yōu)先級列出組合索引中要包括的列。61第61頁，共161頁。CREATEINDEX語法一個組合索引鍵中最多可組合16列。組合索引鍵中的所有列必須在同一個表或視圖中。組合索引值允許的最大大小為900字節(jié)。不能將大型對象(LOB)數(shù)據(jù)類型ntext、text、varchar(max)、nvarchar(max)、varbinary(max)、xml或image的列指定為索引的鍵列。62第62頁，共161頁。CREATEINDEX語法ASC/DESC這兩個選項允許在索引的升序排列和降序排列之間進行選擇默認選項為ASC，它是升序排列63第63頁，共161頁。INCLUDEINCLUDE(column[,...n])指定要添加到非聚集索引的葉級別的非鍵列。非聚集索引可以唯一，也可以不唯一?？砂姆擎I列的最大數(shù)量為1,023列；最小數(shù)量為1列。INCLUDE列表中列名不能重復，且列不能同時用作鍵列和非鍵列。64第64頁，共161頁。WITH選項填充因子PAD_INDEX={ON|OFF}ON：用fillfactor指定的可用空間百分比應用于索引的中間級頁。OFF或不指定fillfactor：使用默認設置填充索引中間級。PAD_INDEX選項只有在指定了FILLFACTOR時才有用，因為PAD_INDEX使用由FILLFACTOR所指定的百分比。65第65頁，共161頁。WITH選項填充因子FILLFACTOR=fillfactor指定在創(chuàng)建或重新生成索引的過程中數(shù)據(jù)庫引擎

使每個索引頁的葉級別的填充程度。fillfactor必須為1至100之間的整數(shù)值。默認值為0。如果fillfactor為100或0，則數(shù)據(jù)庫引擎會創(chuàng)建葉級頁的填充已達到其容量的索引，但在索引樹的較高級別中預留空間，以容納至少一個額外的索引行。注意：填充因子取值為0和100是相同的。66第66頁，共161頁。WITHIGNORE_DUP_KEY指定對唯一聚集索引或唯一非聚集索引執(zhí)行多行插入操作時出現(xiàn)重復鍵值的錯誤響應。默認為OFFON：發(fā)出一條警告信息，但只有違反了唯一索引的行才會失敗。OFF：發(fā)出錯誤消息，并回滾整個INSERT事務IGNORE_DUP_KEY設置僅適用于創(chuàng)建或重新生成索引后發(fā)生的插入操作。67第67頁，共161頁。WITHIGNORE_DUP_KEY例如，如果一個單條語句向某個具有唯一索引的表中插入20行，而其中10行含有重復鍵值，默認情況下（OFF）全部20行都將被拒絕。如果索引選項IGNORE_DUP_KEY設置為ON，則只有10個重復鍵值會被拒絕，其他10個非重復鍵值將插入到表中。68第68頁，共161頁。WITHDROP_EXISTING重新創(chuàng)建索引：具有所討論名稱的任何現(xiàn)有索引將在構造新索引之前被刪除。當和聚集索引一起使用該選項時，這個選項比簡單刪除并重新創(chuàng)建現(xiàn)有的索引更加有效。刪除舊索引然后重新創(chuàng)建同一索引：所有非聚集索引都被刪除和重新創(chuàng)建兩次。重新創(chuàng)建索引，會告訴SQLServer要重新組織現(xiàn)有索引，避免了刪除和重新創(chuàng)建非聚集索引這些不必要的工作。另一個重要優(yōu)點是可以使用現(xiàn)有索引中的數(shù)據(jù)排序次序，從而避免了對數(shù)據(jù)重新排序。69第69頁，共161頁。WITH選項SORT_IN_TEMPDB只有在tempdb存儲在與數(shù)據(jù)庫(該數(shù)據(jù)庫將包含新的索引)物理上分離的驅(qū)動器上時，該選項才有意義SQL在臨時的tempdb數(shù)據(jù)庫中對保存在索引中的數(shù)據(jù)進行排序，可實現(xiàn)并行的磁盤操作提示：如果要使用SORT_IN_TEMPDB，則確保在tempdb內(nèi)有用于大文件的足夠空間。70第70頁，共161頁。WITH選項ONLINE={ON|OFF}指定在索引操作期間基礎表和關聯(lián)的索引是否可用于查詢和數(shù)據(jù)修改操作。默認值為OFF。聯(lián)機索引操作只適用于SQLServer2005EnterpriseEdition。71第71頁，共161頁。WITH選項ONSQLServer提供了從通過使用ON選項的數(shù)據(jù)中獨立存儲索引的選項。好處：索引需要的空間可以擴展到其他的驅(qū)動器中。索引操作的I/O不會承擔物理數(shù)據(jù)的檢索工作。filegroup在指定文件組中保存索引。如果未指定位置，則索引將與基礎表或視圖使用相同的文件組PRIMARY是保留關鍵字，作為對象名稱要加括號72第72頁，共161頁。P133【例7-1】按employee表的employee_name列建立非聚集索引。CREATENONCLUSTEREDINDEXname_idxONemployee(employee_name)73第73頁，共161頁。創(chuàng)建覆蓋性索引USEAdventureWorks;GOCREATEINDEXIX_Address_PostalCodeONPerson.Address(PostalCode)INCLUDE(AddressLine1,AddressLine2,City,StateProvinceID);74第74頁，共161頁。查看與修改索引【例7-2】使用系統(tǒng)存儲過程sp_helpindex查看表employee的索引。USESalesGOEXECsp_helpindexemployeeGO75第75頁，共161頁。查看與修改索引使用ALTERINDEX語句可以重新生成索引、重新組織索引或者禁止索引。ALTERINDEX{index_name|ALL}

ON<object>

{REBUILD

|DISABLE

|REORGANIZE}76第76頁，共161頁。ALTERINDEX語句ReorganizeIndex(重新組織索引)指定將使用相同的列、索引類型、唯一性屬性和排序順序重新生成索引RebuildIndex(重新生成索引)重新生成數(shù)據(jù)庫表和視圖中的索引。一般碎片<=30%時，使用重新組織的方法速度比索引重建快；碎片>30%時，索引重建的速度比重新組織要快。77第77頁，共161頁。ALTERINDEX語句ALTERINDEX不能用于對索引重新分區(qū)或?qū)⑺饕频狡渌募M。此語句不能用于修改索引定義，如添加或刪除列，或更改列的順序，使用帶有DROP_EXISTING子句的CREATEINDEX執(zhí)行這些操作。78第78頁，共161頁。P135【例7-3】更改employee表中索引name_idx名稱為employee_index_name。USESalesGOEXECsp_rename'_idx','employee_index_name','index'79第79頁，共161頁。刪除索引DROPINDEXP135【例7-4】刪除employee表內(nèi)名為employee_index_name的索引。USESalesIFEXISTS(SELECTnameFROMsysindexesWHEREname='employee_index_name')DROPINDEXemployee.employee_index_nameGO80第80頁，共161頁。補充內(nèi)容：索引的維護當數(shù)據(jù)庫增長的時候，會產(chǎn)生頁面拆分，結果：數(shù)據(jù)頁的邏輯順序?qū)⒉辉倥c物理順序相匹配；許多數(shù)據(jù)頁面只保存它們可以保存數(shù)據(jù)量的小部分我們稱此時索引產(chǎn)生了碎片例如連續(xù)的10行數(shù)據(jù)放置在10個頁面中，則SQLServer必須加載10個單獨的頁面以獲取信息。更多的頁面等于更多的讀操作81第81頁，共161頁。索引的維護碎片有它的好的方面——OLTP系統(tǒng)喜歡碎片在其中沒有許多數(shù)據(jù)的頁面可以插入數(shù)據(jù)，而只有很少的或者不需要擔心的頁面拆分。OLAP系統(tǒng)不喜歡碎片大量的碎片導致較差的讀性能，但也導致極好的插入性能UPDATE和DELETE語句同樣會導致索引碎片。82第82頁，共161頁。碎片為減少索引碎片，使用FILLFACTOR選項指定索引在創(chuàng)建時的葉級頁應被填充的比例填充因子越低，葉子級頁在不被拆分的情況下容納的條目更多，碎片發(fā)生的可能性因而越低較低的填充因子會使每一個葉子級頁的初始存儲數(shù)據(jù)變少，因而會產(chǎn)生較大的索引。83第83頁，共161頁。碎片如果被索引的表不是只讀表，索引早晚會產(chǎn)生碎片具有碎片的索引可以使用語句ALTERINDEX進行碎片整理以提高數(shù)據(jù)訪問速度。在索引屬性的“碎片”選項卡中選擇“重新組織索引”會在SQLServer重新組織索引時減少碎片84第84頁，共161頁。查看索引碎片信息兩種方式查看有關索引的碎片信息sys.dm_db_index_physical_stats系統(tǒng)函數(shù)使用圖形化工具：查看索引的屬性85第85頁，共161頁。系統(tǒng)如何使用索引在查詢語句中，SQLServer系統(tǒng)是如何判斷是否使用索引及使用哪些索引呢？一般地，系統(tǒng)是根據(jù)索引的選擇性和索引類型。如果索引列的選擇性很高，也就是說索引列中的只有很少幾行數(shù)據(jù)將被選中，那么應該使用索引。系統(tǒng)如何得到選擇性呢？這就需要系統(tǒng)的統(tǒng)計信息來確定。86第86頁，共161頁。useelectravelcomselect*frombookswheretitle="SQLServer簡明教程"andpublishDate="2007-01-01"如果books表中有100000條記錄，title列上不同的值有30000個，而publishDate列上不同的值只有200個，則查詢優(yōu)化器會選擇建立在title列上的索引，因為它具有較高的命中率87第87頁，共161頁。了解執(zhí)行計劃SQLServer執(zhí)行查詢的時候，首先會確定執(zhí)行該查詢的最佳方式：包括如何且以何種順序來訪問和聯(lián)接數(shù)據(jù)，如何且何時執(zhí)行計算和聚合等等。這些工作由“查詢優(yōu)化器”負責查詢優(yōu)化器使用數(shù)據(jù)分布的統(tǒng)計信息、與查詢涉及的數(shù)據(jù)庫對象相關的元數(shù)據(jù)、索引信息和其他因素來計算多種執(zhí)行計劃的可能性。對于每一種可能，查詢優(yōu)化器會基于數(shù)據(jù)的統(tǒng)計信息估計出執(zhí)行的開銷并選擇執(zhí)行開銷最小的計劃88第88頁，共161頁。了解執(zhí)行計劃對于有些查詢來說，執(zhí)行這個計算的過程可能超過了按效率最低的計劃執(zhí)行所花費的時間，因此，SQLServer并不計算每一個查詢的所有計劃。SQLServer有一個復雜的算法來找出一個合理的、接近最小可能開銷的計劃。計劃生成之后，被存儲在一個緩存器中，隨后由數(shù)據(jù)庫引擎按該計劃所指示的方式執(zhí)行89第89頁，共161頁。優(yōu)化查詢查詢優(yōu)化器能否針對給定的查詢生成高效的執(zhí)行計劃，取決于以下兩個因素：索引數(shù)據(jù)庫索引提供了在表中快速查詢特定行的能力在表中索引的支持下，查詢優(yōu)化器可以找出并使用正確的索引來優(yōu)化對數(shù)據(jù)的訪問如果沒有索引，數(shù)據(jù)庫引擎只有一個選擇：對表中的數(shù)據(jù)進行全部掃描以找出要找的數(shù)據(jù)行90第90頁，共161頁。優(yōu)化查詢查詢優(yōu)化器能否針對給定的查詢生成高效的執(zhí)行計劃，取決于以下兩個因素：數(shù)據(jù)的分布統(tǒng)計如果統(tǒng)計信息丟失或者過時了，查詢優(yōu)化器就無法計算出高效的執(zhí)行計劃。在許多情況下，統(tǒng)計信息會自動生成并更新。91第91頁，共161頁。查看查詢執(zhí)行計劃1、在SSMS中，新建一個查詢窗口，選擇當前上下文數(shù)據(jù)庫為“AdventureWorks”2、執(zhí)行以下SELECT語句USEAdventureWorksSELECTSalesOrderID,OrderQTYFROMSales.SalesOrderDetailWHEREProductID=712ORDERBYOrderQTYDESC92第92頁，共161頁。93第93頁，共161頁。查看查詢執(zhí)行計劃在“查詢”菜單上選擇“顯示估計的執(zhí)行計劃”顯示這個查詢的執(zhí)行計劃。執(zhí)行計劃如下所示94第94頁，共161頁。查看查詢執(zhí)行計劃閱讀執(zhí)行計劃：從右向左，從上向下閱讀圖標：代表計劃中的一個運算符箭頭：表示在這些運算符之間的數(shù)據(jù)交換過程箭頭的寬度代表運算符之間傳遞數(shù)據(jù)的數(shù)量95第95頁，共161頁。查看查詢執(zhí)行計劃SQLServer使用聚集索引掃描來訪問數(shù)據(jù)數(shù)據(jù)隨后傳遞到排序運算符，它將根據(jù)語句的ORDERBY子句來對數(shù)據(jù)進行排序數(shù)據(jù)隨后發(fā)送至客戶96第96頁，共161頁。查看查詢執(zhí)行計劃操作符圖標下面的開銷百分比顯示了這個操作在查詢總開銷中所占的百分比可了解哪一個操作使用的執(zhí)行資源最多本例中，“聚集索引掃描”的開銷最大，占此查詢總開銷的89%97第97頁，共161頁。查看查詢執(zhí)行計劃鼠標放在“聚集索引掃描”操作符上：黃色窗口提供了此操作的詳細信息SQLServer基于Sales.SalesOrderDetail表中的聚集索引執(zhí)行一次聚集索引掃描操作來找出ProductID為712的記錄。98第98頁，共161頁。查看查詢執(zhí)行計劃行數(shù)基于SQLServer為該表所存儲的統(tǒng)計信息估計出開銷數(shù)基于統(tǒng)計信息和參考系統(tǒng)的開銷數(shù)得出這些信息只能用于判斷一個操作和其他操作相比是節(jié)約還是昂貴。有關操作符的信息可以通過右鍵單擊圖標，在彈出菜單中選擇“屬性”窗口察看99第99頁，共161頁。實際的執(zhí)行計劃實際的執(zhí)行計劃與估計的執(zhí)行計劃類似，但會包括實際的數(shù)量(不是估計的)，例如行數(shù)和重繞次數(shù)等在“查詢”菜單中選擇“包括實際的執(zhí)行計劃”來包括實際的執(zhí)行計劃。隨后按“F5”執(zhí)行查詢，執(zhí)行計劃將顯示在“執(zhí)行計劃”選項卡中。100第100頁，共161頁。101第101頁，共161頁。檢驗堆結構1.在SSMS中打開一個“新建查詢”窗口并更改數(shù)據(jù)庫上下文為“AdventureWorks”。2.創(chuàng)建兩個表，分別為dbo.Orders和dbo.OrderDetails。102第102頁，共161頁。檢驗堆結構CREATETABLEdbo.Orders(SalesOrderIDintNOTNULL,OrderDatedatetimeNOTNULL,ShipDatedatetimeNULL,StatustinyintNOTNULL,PurchaseOrderNumberdbo.OrderNumberNULL,CustomerIDintNOTNULL,ContactIDintNOTNULL,SalesPersonIDintNULL);103第103頁，共161頁。檢驗堆結構CREATETABLEdbo.OrderDetails(SalesOrderIDintNOTNULL,SalesOrderDetailIDintNOTNULL,CarrierTrackingNumbernvarchar(25),OrderQtysmallintNOTNULL,ProductIDintNOTNULL,UnitPricemoneyNOTNULL,UnitPriceDiscountmoneyNOTNULL,LineTotalAS(isnull((UnitPrice*((1.0)-UnitPriceDiscount))*OrderQty,(0.0))));104第104頁，共161頁。檢驗堆結構INSERTINTOdbo.OrdersSELECTSalesOrderID,OrderDate,ShipDate,Status,PurchaseOrderNumber,CustomerID,ContactID,SalesPersonIDFROMSales.SalesOrderHeader;105第105頁，共161頁。檢驗堆結構INSERTINTOdbo.OrderDetails(SalesOrderID,SalesOrderDetailID,CarrierTrackingNumber,OrderQty,ProductID,UnitPrice,UnitPriceDiscount)SELECTSalesOrderID,SalesOrderDetailID,CarrierTrackingNumber,OrderQty,ProductID,UnitPrice,UnitPriceDiscountFROMSales.SalesOrderDetail;106第106頁，共161頁。檢驗堆結構以上創(chuàng)建了兩張堆結構的表。下面查詢dbo.Orders表，執(zhí)行之前在“查詢”菜單中選擇“包括實際的執(zhí)行計劃”來包括實際的執(zhí)行計劃SETSTATISTICSIOON;SELECT*FROMdbo.OrdersSETSTATISTICSIOOFFSETSTATISTICSIO選項使SQLServer將語句執(zhí)行期間I/O操作的有關信息發(fā)回給用戶。107第107頁，共161頁。消息(31465行受影響)表'Orders'。掃描計數(shù)1，邏輯讀取178次，物理讀取0次，預讀0次，lob邏輯讀取0次，lob物理讀取0次，lob預讀0次。108第108頁，共161頁。檢驗堆結構輸出信息表明SQLServer需要對表中的數(shù)據(jù)進行一次掃描并需要為此操作執(zhí)行178頁的讀操作(邏輯讀)。為了執(zhí)行此操作沒有用到物理讀，此例中數(shù)據(jù)已經(jīng)保存在緩存中如果信息窗口表明對于這個查詢進行了物理讀，再次執(zhí)行此查詢之后會看到物理讀的數(shù)量會比上次執(zhí)行時少。109第109頁，共161頁。檢驗堆結構切換到“執(zhí)行計劃”選項卡。在執(zhí)行計劃中可以看到SQLServer使用一次表掃描操作來訪問數(shù)據(jù)，這是惟一的選擇。110第110頁，共161頁。檢驗堆結構現(xiàn)在對這個查詢稍作修改以獲取特定的行。SETSTATISTICSIOON;SELECT*FROMdbo.OrdersWHERESalesOrderID=46699;SETSTATISTICSIOOFF;111第111頁，共161頁。檢驗堆結構消息表'Orders'。掃描計數(shù)1，邏輯讀取178次，物理讀取0次，預讀0次，lob邏輯讀取0次，lob物理讀取0次，lob預讀0次。系統(tǒng)需要為此查詢讀取178頁并使用表掃描，由于沒有索引可用，只能掃描所有的數(shù)據(jù)來找到需要的行112第112頁，共161頁。使用索引為了提高數(shù)據(jù)訪問的性能，為列定義索引。為了找到具有特定值的索引列所在的行，SQLServer會在索引上查詢這個值并依據(jù)指針來讀取找到的行。相較于使用表掃描時進行的全數(shù)據(jù)掃描，這個操作簡單且節(jié)約得多。113第113頁，共161頁。創(chuàng)建并使用聚集索引1.在SSMS中新建查詢窗口并更改數(shù)據(jù)庫上下文為“AdventureWorks”2.在Orders表上創(chuàng)建一個聚集索引。CREATEUNIQUECLUSTEREDINDEXCLIDX_Orders_SalesOrderIDONdbo.Orders(SalesOrderID)114第114頁，共161頁。創(chuàng)建并使用聚集索引再次執(zhí)行前面執(zhí)行過的兩個SELECT語句來檢驗區(qū)別。要保證在執(zhí)行時包括實際的執(zhí)行計劃。SETSTATISTICSIOON;SELECT*FROMdbo.Orders;SELECT*FROMdbo.OrdersWHERESalesOrderID=46699;SETSTATISTICSIOOFF;115第115頁，共161頁。執(zhí)行計劃116第116頁，共161頁。執(zhí)行計劃由于數(shù)據(jù)不再存儲在堆結構中，SQLServer執(zhí)行了索引操作。執(zhí)行計劃表明將使用兩個主要的索引操作：索引掃描：讀取表中的所有數(shù)據(jù)，通過索引的葉子級所進行的掃描。由于第一個SELECT語句沒有WHERE子句，SQLServer知道需要獲取所有數(shù)據(jù)，這些數(shù)據(jù)存儲在索引的葉子級上。索引查找：SQLServer查找特定值的一個操作。這個操作起始于索引的根并將查詢值傳遞給索引的分支以進行查詢。117第117頁，共161頁。執(zhí)行計劃這兩個操作可以組合起來獲取一個特定范圍的數(shù)據(jù)。在這種局部掃描操作中，SQLServer會首先找到范圍的起始值，然后持續(xù)掃描到范圍的終點值結束。“消息”選項卡(31465行受影響)表'Orders'。掃描計數(shù)1，邏輯讀取180次，物理讀取0次，預讀0次，lob邏輯讀取0次，lob物理讀取0次，lob預讀0次。(1行受影響)表'Orders'。掃描計數(shù)0，邏輯讀取2次，物理讀取0次，預讀0次，lob邏輯讀取0次，lob物理讀取0次，lob預讀0次118第118頁，共161頁。第一個SELECT語句執(zhí)行了與使用堆結構進行表掃描時相同的頁讀取操作。因為SELECT語句要求獲取所有數(shù)據(jù)。因此SQLServer不得不獲取所有數(shù)據(jù)第二個SELECT語句只進行了兩次頁讀取，這比前面有了很大的改進，少讀了178頁。SQLServer只需在索引上進行查找，相較于在所有數(shù)據(jù)頁上進行掃描，它需要的I/O操作要少得多。119第119頁，共161頁。鍵入以下SELECT語句，它會按順序獲取數(shù)據(jù)。SELECT*FROMdbo.OrdersORDERBYSalesOrderID;SELECT*FROMdbo.OrdersORDERBYOrderDate;120第120頁，共161頁。121第121頁，共161頁。第一個語句只執(zhí)行了一次聚集索引掃描而并沒有對數(shù)據(jù)進行排序。因為這個列是一個聚集索引鍵列，數(shù)據(jù)已經(jīng)根據(jù)SalesOrderID進行了排序。因此，SQLServer為了按順序獲取行只需要在葉子級掃描數(shù)據(jù)并返回結果。122第122頁，共161頁。對于第二個查詢，數(shù)據(jù)必須在獲取之后進行排序。在聚集索引掃描操作之后有一個在OrderDate列上進行的排序操作。排序操作非常耗時，因此第二個操作的開銷占據(jù)了整個查詢開銷的93%。對于經(jīng)常需要進行排序的列，最好定義聚集索引經(jīng)常需要進行聚合分組的列也如此，因為在進行聚合數(shù)據(jù)的時候，SQLServer首先需要依據(jù)分組條件對其進行排序123第123頁，共161頁。CREATEUNIQUECLUSTEREDINDEXCLIDX_OrderDetailsONdbo.OrderDetails(SalesOrderID,SalesOrderDetailID)124第124頁，共161頁。SELECT*FROMdbo.OrderDetailsWHERESalesOrderID=46999--查詢SalesOrderID的值為指定值的行SELECT*FROMdbo.OrderDetailsWHERESalesOrderDetailID=14147--查詢SalesOrderDetailID值為指定值的行這兩個列都是CLIDX_OrderDetails索引的索引列125第125頁，共161頁。126第126頁，共161頁。第一個查詢：搜索復合索引第一個列的值，SQLServer使用一個索引查找來找到特定的行。第二個查詢使用一個非常昂貴的索引掃描操作因為這個索引起初是根據(jù)第一個列來進行排序的，不可能只依據(jù)復合索引第二個列找到值安排復合索引中索引列的順序是非常重要的復合索引只適用于附加列與第一個列經(jīng)常被組合查詢或需要通過幾個列來強制惟一性的時候。127第127頁，共161頁。第7章索引與視圖下視圖——虛擬表128第128頁，共161頁。為什么要創(chuàng)建視圖在Northwind(SQLServer2000的示例數(shù)據(jù)庫)數(shù)據(jù)庫中，如果要查看一個訂單是由哪個雇員簽訂的，簽訂的時間是什么時候，買了什么產(chǎn)品，價格是多少，由誰來送貨，送貨的地址是什么等內(nèi)容，那么就要從五個表中查詢記錄產(chǎn)品訂單訂單明細雇員運貨商129第129頁，共161頁。為什么要創(chuàng)建視圖SELECT訂單.訂單ID,雇員.姓氏,雇員.名字,產(chǎn)品.產(chǎn)品名稱,

訂單明細.單價,訂單明細.數(shù)量,訂單明細.折扣,運貨商.公司名稱,

訂單.貨主名稱,訂單.貨主地址,訂單.貨主城市,訂單.訂購日期,

訂單.發(fā)貨日期FROM訂單INNERJOIN

訂單明細ON訂單.訂單ID=訂單明細.訂單IDINNERJOIN

雇員ON訂單.雇員ID=雇員.雇員IDINNERJOIN

產(chǎn)品ON訂單明細.產(chǎn)品ID=產(chǎn)品.產(chǎn)品IDINNERJOIN

運貨商ON訂單.運貨商=運貨商.運貨商ID130第130頁，共161頁。為什么要創(chuàng)建視圖如果要查詢具體某一個訂單的詳細內(nèi)容，還要在其后增加一個“WHERE訂單.訂單ID=”的語句。如果經(jīng)常需要查詢相同的字段內(nèi)容（只是條件不同，如上例中可能只是訂單的ID號不同而已），每次都重復地寫這么一大串相同的代碼，無疑會增加工作量和影響工作效率。131第131頁，共161頁。為什么要創(chuàng)建視圖如果將這個查詢的結果集視為一個表，那么這個表就是一個視圖：CREATEVIEW訂單詳細視圖ASSELECT訂單.訂單ID,雇員.姓氏,雇員.名字,產(chǎn)品.產(chǎn)品名稱,

訂單明細.單價,訂單明細.數(shù)量,訂單明細.折扣,運貨商.公司名稱,

訂單.貨主名稱,訂單.貨主地址,訂單.貨主城市,訂單.訂購日期,

訂單.發(fā)貨日期FROM訂單INNERJOIN

訂單明細ON訂單.訂單ID=訂單明細.訂單IDINNERJOIN

雇員ON訂單.雇員ID=雇員.雇員IDINNERJOIN

產(chǎn)品ON訂單明細.產(chǎn)品ID=產(chǎn)品.產(chǎn)品IDINNERJOIN

運貨商ON訂單.運貨商=運貨商.運貨商ID132第132頁，共161頁。為什么要創(chuàng)建視圖創(chuàng)建完視圖之后，查詢某個訂單的詳細情況，只要輸入以下代碼，不用輸入一長串代碼了。

SELECT*FROM訂單詳細視圖

WHERE訂單ID=10248133第133頁，共161頁。視圖視圖是一個命名的虛擬表（virtualtable），它由一個查詢來定義，可以當作表使用。與持久表（permanenttable）不同的是，視圖中的數(shù)據(jù)沒有物理表現(xiàn)形式，除非為其創(chuàng)建索引。當在一個未建索引的視圖上執(zhí)行查詢時，SQLServer實際訪問的是基礎表（underlyingtable）視圖具備了數(shù)據(jù)表的一些特性，數(shù)據(jù)表可以完成的功能，如查詢、修改、刪除等操作，在視圖中都可以完成。134第134頁，共161頁。視圖視圖的重要用途之一便是被用作一個抽象裝置可以利用視圖訪問經(jīng)過篩選和處理的數(shù)據(jù)，而不是直接對基表（basetable）進行操作，可以把視圖作為一個安全層（securitylayer）135第135頁，共161頁。使用視圖的優(yōu)點使用視圖有以下幾個優(yōu)點：簡化查詢語句：通過視圖可以將復雜的查詢語句變得很簡單。增加可讀性：視圖中可以只顯示有用的字段，可使用字段別名，能方便用戶瀏覽查詢的結果。方便程序的維護：如應用程序使用視圖來存取數(shù)據(jù)，那么當數(shù)據(jù)表的結構發(fā)生改變時，只需要更新視圖存儲的查詢語句即可，不需要更改程序136第136頁，共161頁。使用視圖的優(yōu)點使用視圖有以下幾個優(yōu)點：增加數(shù)據(jù)的安全性和保密性：針對不同的用戶，可以創(chuàng)建不同的視圖，此時的用戶只能查看和修改其所能看到的視圖中的數(shù)據(jù)，而真正的數(shù)據(jù)表中的數(shù)據(jù)甚至連數(shù)據(jù)表都是不可見不可訪問的，這樣可以限制用戶瀏覽和操作的數(shù)據(jù)內(nèi)容。另外視圖所引用的表的訪問權限與視圖的權限設置也是相互不影響的。137第137頁，共161頁。創(chuàng)建視圖SQLServer2005中，主要使用CREATEVIEW語句創(chuàng)建視圖。CREATEVIEW[schema_name.]view_name

--架構名.視圖名

[(column,...n])]

--列名[WITH<view_attribute>[,...n]]ASselect_statement;]

--查詢語句[WITHCHECKOPTION]

138第138頁，共161頁。CREATEVIEW語法<view_attribute>::={[ENCRYPTION]

--加密

[SCHEMABINDING]

--綁定架構

[VIEW_METADATA]

}

--返回有關視圖的元數(shù)據(jù)信息ENCRYPTION：加密視圖。SCHEMABINDING：將視圖綁定到基礎表的架構139第139頁，共161頁。CREATEVIEW語法schema_name：視圖所屬架構名view_name：視圖名column：視圖中所使用的列名，一般只有列是從算術表達式、函數(shù)或常量派生出來的或者列的指定名稱不同于來源列的名稱時，才需要使用。select_statement：搜索語句。WITHCHECKOPTION：強制針對視圖執(zhí)行的所有數(shù)據(jù)修改語句都必須符合在select_statement中設置的條件。WITHCHECKOPTION可確保提交修改后，仍可通過視圖看到數(shù)據(jù)。140第140頁，共161頁。加密視圖定義在SQLServer2005中每個數(shù)據(jù)庫的系統(tǒng)視圖里都有一個名為“INFORMATION_SCHEMA.VIEWS”的視圖，其中記錄了該數(shù)據(jù)庫中所有視圖的信息，使用SELECT*FROMINFORMATION_SCHEMA.VIEWS可以查看該視圖內(nèi)容如果不想讓別人看到該視圖里的內(nèi)容，可以使用withencryption參數(shù)來為視圖加密。141第141頁，共161頁。加密視圖創(chuàng)建完加密視圖之后，在SSMS中不能對其修改可以使用alterview語句修改加密視圖。使用alterview語句修改視圖和使用SSMS修改視圖不同，它不需要先顯示視圖的代碼。142第142頁，共161頁。綁定所引用的基礎表由于視圖和數(shù)據(jù)表是數(shù)據(jù)庫中獨立的兩種對象，雖然視圖要引用數(shù)據(jù)表，但是當引用的數(shù)據(jù)表刪除或修改時，視圖本身并不會被刪除或修改，因此往往在刪除數(shù)據(jù)表之后，會引起視圖運行錯誤。在創(chuàng)建視圖時使用withSCHEMABINDING參數(shù)，可以防止引用的數(shù)據(jù)表或視圖刪除或修改。143第143頁，共161頁。SCHEMABINDINGSCHEMABINDING選項把視圖或UDF的架構綁定到基對象的架構。如果使用SCHEMABINDING選項創(chuàng)建視圖，SQLServer將不允許刪除基對象或修改被引用的列。這個選項對于定義視圖的查詢有兩個語法要求：所有對象必須使用由兩部分構成的名稱（例如，應該使用dbo.Orders，不能是Orders），而且不能在SELECT列表中使用*，所有的列名稱都必須被顯式指定。144第144頁，共161頁。WITHCHECKOPTION以下示例顯示名為SeattleOnly的視圖，此視圖引用了五個表，并允許進行數(shù)據(jù)修改，以便僅適用于居住在西雅圖的雇員。

CREATEVIEWSeattleOnlyASSELECTc.LastName,c.FirstName,a.City,s.StateProvinceCodeFROMPerson.ContactcJOINHumanResources.EmployeeeONc.ContactID=e.ContactID145第145頁，共161頁。WITHCHECKOPTIONJOINHumanResources.EmployeeAddresseaONe.EmployeeID=ea.EmployeeIDJOINPerson.AddressaONea.AddressID=a.AddressIDJOINPerson.StateProvincesONa.StateProvinceID=s.StateProvinceIDWHEREa.City='Seattle'WITHCHECKOPTION使用WITHCHECKOPTION選項系統(tǒng)將會檢查修改后的數(shù)據(jù)是否符合視圖定義中的查詢條件146第146頁，共161頁。視圖的限制P137在用createview創(chuàng)建視圖時，select子句里不能包括以下內(nèi)容：不能包括compute、computeby子句不能包括orderby子句，除非在select子句里有top子句不能包括into關鍵字不能引用臨時表或表變量147第147頁，共161頁。使用CREATEVIEW語句例7-6在Sales數(shù)據(jù)庫中創(chuàng)建sell_view視圖，該視圖選擇3個基表(employee,goods,sell_order)中的數(shù)據(jù)來顯示員工銷售貨物情況的虛擬表。

CREATEVIEWsell_viewAS148第148頁，共161頁。使用CREATEVIEW語句SELECTemployee.employee_name,employee.employee_id,sell_order.order_num,sell_order.discount,goods.goods_name,goods.unit_price,sell_o