1、第8章 索引與視圖1本章內容8.1 使用索引8.2 使用視圖28.1 使用索引索引是與表或視圖關聯的磁盤上結構，索引中的鍵存儲在一個結構中，使SQL Server可以快速有效地查找與鍵值關聯的行。 建立索引的優點：索引可以減少為返回查詢結果集而必須讀取的數據量；索引還可以強制表中的行具有唯一性，從而確保表數據的數據完整性；使用索引可以快速找到表或索引視圖中的特定信息 。38.1 使用索引索引的分類表或視圖的索引可以包括以下幾類：(1) 惟一索引在表中建立惟一性索引時，組成該索引的字段或字段組合在表中具有惟一值，也就是說，對于表中的任何兩行記錄來說，索引鍵的值都是各不相同。 48.1 使用索引(

2、2) 聚集索引聚集索引根據數據行的鍵值在表或視圖中排序和存儲這些數據行，即聚集索引決定了數據的物理順序。只有在表中建立了一個聚集索引后，數據才會按照索引鍵值指定的順序存儲到表中。由于一個表中的數據只能按照一種順序來存儲，所以在一個表中只能建立一個聚集索引。 表具有聚集索引，則該表稱為聚集表。否則，其數據行存儲在一個稱為堆的無序結構中 。58.1 使用索引(3) 非聚集索引非聚集索引包含非聚集索引鍵值，并且每個鍵值項都有指向包含該鍵值的數據行的指針。具有獨立于數據行的結構 。從非聚集索引中的索引行指向數據行的指針稱為行定位器。行定位器的結構取決于數據頁是存儲在堆中還是聚集表中。對于堆，行定位器是

3、指向行的指針。對于聚集表，行定位器是聚集索引鍵。注意：聚集索引和非聚集索引都可以是唯一的，即任何兩行都不能有相同的索引鍵值 ；索引也可以不是唯一的，即多行可以共享同一鍵值 ；每當修改了表數據后，都會自動維護表或視圖的索引 。 68.1 使用索引(4) 包含性列索引 一種非聚集索引，它擴展后不僅包含鍵列，還包含非鍵列 。(5) 索引視圖 視圖的索引將具體化(執行)視圖，并將結果集永久存儲在唯一的聚集索引中，而且其存儲方法與帶聚集索引的表的存儲方法相同。創建聚集索引后，可以為視圖添加非聚集索引。78.1 使用索引(6) 全文索引 一種特殊類型的基于標記的功能性索引，由Microsoft SQL S

4、erver全文引擎(MSFTESQL)服務創建和維護，用于幫助在字符串數據中搜索復雜的詞。(7) XML索引 xml數據類型列中XML二進制大型對象的已拆分持久表示形式 。注意：對表中的列定義了PRIMARY KEY約束和UNIQUE約束時，會自動創建索引。 88.1 使用索引索引設計準則 要設計出好的索引集，需要了解數據庫、查詢和數據列的特征 。(1) 數據庫準則 避免對經常更新的表創建過多的索引，并且列要盡可能少；使用多個索引可以提高更新少而數據量大的查詢的性能。對小表進行索引可能不會產生優化效果 。當視圖包含聚合、表聯接或聚合和聯接的組合時，視圖的索引可以顯著地提升性能 。 98.1 使

5、用索引(2) 查詢準則為經常用于查詢中的謂詞和聯接條件的所有列創建非聚集索引。涵蓋索引可以提高查詢性能，因為符合查詢要求的全部數據都存在于索引本身中 。將插入或修改盡可能多的行的查詢寫入單個語句內，而不要使用多個查詢更新相同的行。 評估查詢類型以及如何在查詢中使用列 。例如，在完全匹配查詢類型中使用的列就適合用于非聚集索引或聚集索引。108.1 使用索引(3) 列準則對于聚集索引，應保持較短的索引鍵長度。另外，對唯一列或非空列創建聚集索引可以使聚集索引效率高 .不能將ntext、text、image、varchar(max)、nvarchar(max)和varbinary(max)數據類型的列

6、指定為索引鍵列 ，不過，varchar(max)、nvarchar(max)、varbinary(max)和xml數據類型的列可以作為非鍵索引列參與非聚集索引。xml數據類型的列只能在XML索引中用作鍵列 。 118.1 使用索引如果索引包含多個列，則應考慮列的順序 。用于等于 (=)、大于 ()、小于 () 或BETWEEN搜索條件的WHERE子句或者參與聯接的列應該放在最前面。其他列應該基于其非重復級別進行排序，就是說，從最不重復的列到最重復的列。 考慮對計算列進行索引 。128.1 使用索引(4) 索引特征在確定某一索引適合某一查詢之后，可以選擇最適合具體情況的索引類型。創建索引時需確定

7、以下選項。聚集還是非聚集唯一還是非唯一單列還是多列索引中的列是升序排序還是降序排序 138.1 使用索引 創建索引1. 創建索引步驟如下：(1) 設計索引 索引設計包括確定要使用的列，選擇索引類型(例如聚集或非聚集)，選擇適當的索引選項，以及確定文件組或分區方案布置 。(2) 確定最佳的創建方法 使用CREATE TABLE或ALTER TABLE對列定義PRIMARY KEY或UNIQUE約束。148.1 使用索引默認情況下，創建的唯一聚集索引可以強制PRIMARY KEY約束，除非表中已存在聚集索引或指定了唯一的非聚集索引。默認情況下，創建的唯一非聚集索引可以強制UNIQUE約束，除非已明

8、確指定唯一的聚集索引且表中不存在聚集索引。 使用CREATE INDEX語句或SQL Server Management Studio對象資源管理器中的“新建索引”對話框創建獨立于約束的索引 。必須指定索引的名稱、表以及應用該索引的列。還可以指定索引選項和索引位置、文件組或分區方案。默認情況下，如果未指定聚集或唯一選項，將創建非聚集的非唯一索引 158.1 使用索引(3) 創建索引一個重要因素需要考慮：是對空表還是對包含數據的表創建索引。對空表創建索引在創建索引時不會對性能產生任何影響，而向表中添加數據時，會對性能產生影響。創建索引后，索引將自動啟用并可以使用?？梢酝ㄟ^禁用索引來刪除對該索引的

9、訪問。168.1 使用索引2. 創建聚集索引用途：聚集索引可以提高查詢性能，還可以按需重新生成或重新組織來控制表碎片，所以除了個別表之外，每個表都應該有聚集索引。也可以對視圖創建聚集索引 。聚集索引按下列方式實現 ： PRIMARY KEY和UNIQUE約束在創建PRIMARY KEY約束時，如果不存在該表的聚集索引且未指定唯一非聚集索引，則將自動對一列或多列創建唯一聚集索引。主鍵列不允許空值。在創建UNIQUE約束時，默認情況下將創建唯一非聚集索引，以便強制UNIQUE約束。如果不存在該表的聚集索引，則可以指定唯一聚集索引。178.1 使用索引將索引創建為約束的一部分后，會自動將索引命名為與

10、約束名稱相同的名稱。 獨立于約束的索引 指定非聚集主鍵約束后，可以對非主鍵列的列創建聚集索引 索引視圖若要創建索引視圖，需要對一個或多個視圖列定義唯一聚集索引。視圖將具體化，并且結果集存儲在該索引的頁級別中，其存儲方式與表數據存儲在聚集索引中的方式相同 。188.1 使用索引3. 創建非聚集索引通常，創建非聚集索引是為了提高聚集索引未包含的常用查詢的性能?？梢酝ㄟ^下列方法實現非聚集索引 ：PRIMARY KEY和UNIQUE約束獨立于約束的索引：默認情況下，如果未指定聚集，將創建非聚集索引 。索引視圖的非聚集索引：對視圖創建唯一的聚集索引后，便可以創建非聚集索引 。198.1 使用索引4. 創

11、建唯一索引創建唯一索引可以確保任何生成重復鍵值的嘗試都會失敗。創建UNIQUE約束和創建與約束無關的唯一索引并沒有明顯的區別 。唯一索引可通過以下方式實現 ： PRIMARY KEY或UNIQUE約束 獨立于約束的索引：可以為一個表定義多個唯一非聚集索引 索引視圖如果在鍵列中存在重復值，將無法創建唯一索引或約束 208.1 使用索引5. 使用Transact-SQL語句創建索引 使用CREATE INDEX語句可以創建索引，語法格式如下 CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX index_name ON ( column ASC | DESC

12、,.n ) ; := database_name. schema_name . | schema_name. table_or_view_name218.1 使用索引 UNIQUE：為表或視圖創建唯一索引。唯一索引不允許兩行具有相同的索引鍵值。視圖的聚集索引必須唯一。 CLUSTERED：為表或視圖創建聚集索引 。一個表或視圖只允許同時有一個聚集索引。為一個視圖創建唯一聚集索引會在物理上具體化該視圖。必須先為視圖創建唯一聚集索引，然后才能為該視圖定義其他索引 。 NONCLUSTERED：為表或視圖創建非聚集索引 。對于非聚集索引，數據行的物理排序獨立于索引排序 。每個表都最多可包含249個非

13、聚集索引 。默認值為NONCLUSTERED 228.1 使用索引 index_name：索引的名稱 。在表或視圖中必須唯一，但在數據庫中不必唯一。索引名稱必須符合標識符的規則 。主XML索引名不得以#、#、 或字符開頭。 column：索引所基于的一列或多列 。指定兩個或多個列名，可為指定列的組合值創建組合索引。在table_or_view_name后的括號中，按排序優先級列出組合索引中要包括的列 。一個組合索引鍵中最多可組合16列。組合索引鍵中的所有列必須在同一個表或視圖中 。238.1 使用索引 ASC | DESC ：確定特定索引列的升序或降序排序方式。默認值為ASC 。 ：要為其建立

14、索引的完全限定對象或非完全限定對象。其中選項含義同其他命令。例如：創建簡單非聚集索引。以下示例為teaching數據庫中student表的AGE列創建非聚集索引。USE teachingGOCREATE INDEX IX_age ON student (AGE)GO執行結果如圖8.1所示。248.1 使用索引6. 使用圖形工具創建索引 具體步驟詳見教材258.1 使用索引 修改索引1. 禁止索引索引定義保留在元數據中，非聚集索引的索引統計信息仍保留。對視圖禁用非聚集索引或聚集索引會以物理方式刪除索引數據。禁用表的聚集索引可以防止對數據的訪問，數據仍保留在表中，但在刪除或重新生成索引之前，無法對

15、這些數據執行DML操作。 在以下情況中可能禁用一個或多個索引 ：(1) SQL Server 2005 Database Engine在SQL Server升級期間自動禁用索引 。(2) 使用ALTER INDEX手動禁用索引 。268.1 使用索引ALTER INDEX命令語句的語法格式如下： ALTER INDEX index_name | ALL ON DISABLE各選項含義如下：ALL：指定與表或視圖相關聯的所有索引，而不考慮索引類型。 DISABLE：將索引標記為禁用，從而不能由SQL Server 2005 Database Engine使用 。已禁用索引的索引定義保留在沒有基礎

16、索引數據的系統目錄中 。禁用聚集索引將阻止用戶訪問基礎表數據。278.1 使用索引例如：禁用索引。下面的示例禁用了對course表的IX_Cname索引。USE teachingGOALTER INDEX IX_Cname ON course DISABLEGO288.1 使用索引(3) 使用圖形工具禁用索引 具體步驟詳見教材298.1 使用索引2. 啟用索引索引被禁用后一直保持禁用狀態，直到它重新生成或刪除 ?？梢允褂孟铝蟹椒ㄖ?，重新生成禁用的索引來啟用它。(1) 帶REBUILD子句的ALTER INDEX語句 ALTER INDEX命令語句的語法格式如下。 ALTER INDEX in

17、dex_name | ALL ON REBUILD308.1 使用索引REBUILD：指定將使用相同的列、索引類型、唯一性屬性和排序順序重新生成索引。REBUILD啟用已禁用的索引。 例如：重新生成索引。以下示例在Course表中重新生成索引IX_Cname。USE teachingGOALTER INDEX IX_Cname ON course REBUILDGO318.1 使用索引(2) 使用圖形工具啟用索引 具體步驟詳見教材328.1 使用索引3. 重命名索引重命名索引將用提供的新名稱替換當前的索引名稱 。(1) 使用sp_rename系統過程重命名索引 sp_rename系統過程的語法

18、格式如下。 sp_rename objname = object_name , newname = new_name , objtype = object_type 338.1 使用索引 objname = object_name：用戶對象或數據類型的當前限定或非限定名稱 。如果要重命名的對象是表中的列，則object_name的格式必須是table.column。如果要重命名的對象是索引，則object_name的格式必須是table.index。 newname = new_name：指定對象的新名稱 。new_name的數據類型為sysname，無默認值 。 objtype = obje

19、ct_type：要重命名的對象的類型 。object_type的數據類型為 varchar(13)，默認值為NULL，可取COLUMN、DATABASE、INDEX、OBJECT、USERDATATYPE，具體見表8.1。348.1 使用索引例如：重命名索引。以下示例將course表中的IX_Cname索引重命名為IX_Course_Cname。USE teachingGOEXEC sp_rename N IX_Cname , N IX_Course_Cname , N INDEX (2) 使用圖形工具重命名索引 具體步驟詳見教材358.1 使用索引 刪除索引當一個索引不再需要時，可以將其從數

20、據庫中刪除，以回收它當前使用的磁盤空間。以便數據庫中的任何對象都可以使用此回收的空間。必須先刪除PRIMARY KEY或UNIQUE約束，才能刪除約束使用的索引 。刪除視圖或表時，將自動刪除為永久性和臨時性視圖或表創建的索引 。刪除聚集索引后，存儲在聚集索引葉級中的數據行將存儲在未排序的表(堆)中 。刪除索引視圖的聚集索引時，將自動刪除同一視圖的所有非聚集索引和自動創建的統計信息。手動創建的統計信息不會刪除。368.1 使用索引1. 使用Transact-SQL語句刪除索引 可以使用DROP INDEX命令刪除索引，語句的語法格式如下。DROP INDEX index_name ON DROP

21、 INDEX語句不適用于通過定義PRIMARY KEY或UNIQUE約束創建的索引。這些約束是分別使用CREATE TABLE或ALTER TABLE語句的PRIMARY KEY或UNIQUE選項創建的。378.1 使用索引例如：刪除索引。下列示例刪除了student表中的IX_age索引。 USE teachingGODROP INDEX IX_age ON studentGO2. 使用圖形工具刪除索引 具體步驟詳見教材388.2 使用視圖視圖可以被看成是虛擬表或存儲查詢。除非是索引視圖，否則視圖的數據不會作為非重復對象存儲在數據庫中。SELECT語句的結果集構成視圖所返回的虛擬表。用戶可以

22、采用引用表時所使用的方法，在Transact-SQL語句中引用視圖名稱來使用此虛擬表。398.2 使用視圖 視圖的作用視圖是一個虛擬表，其內容由查詢定義 。通過視圖進行查詢沒有任何限制，通過它們進行數據修改時的限制也很少 。(1) 視圖的類型標準視圖 標準視圖組合了一個或多個表中的數據，可以獲得使用視圖的大多數好處 索引視圖 分區視圖 分區視圖在一臺或多臺服務器間水平連接一組成員表中的分區數據 408.2 使用視圖(2) 視圖的使用視圖可用作安全機制，它可以允許用戶通過視圖訪問數據，而不授予用戶直接訪問視圖基礎表的權限 使用視圖有以下作用 ：著重于特定數據 視圖使用戶能夠著重于所感興趣的特定數

23、據和所負責的特定任務。不必要的數據或敏感數據可以不出現在視圖中 。簡化數據操作 可以將常用聯接、投影、UNION查詢和SELECT查詢定義為視圖，以便使用戶不必在每次對該數據執行附加操作時指定所有條件和條件限定。 418.2 使用視圖 提供向后兼容性視圖能夠在表的架構更改時為表創建向后兼容接口 。自定義數據 視圖允許用戶以不同方式查看數據，即使在他們同時使用相同的數據時也是如此。這在具有許多不同目的和技術水平的用戶共用同一數據庫時尤其有用。 例如，可創建一個視圖以僅檢索由客戶經理處理的客戶數據。該視圖可以根據使用它的客戶經理的登錄ID決定檢索哪些數據。428.2 使用視圖導出和導入數據 可使用

24、視圖將數據導出到其他應用程序。例如，可將teaching數據庫中的student表、course表及s_c表導出至Microsoft Excel中進行數據分析 ?？绶掌鹘M合分區數據 Transact-SQL UNION集合運算符可在視圖內使用，將單獨表的兩個或多個查詢的結果組合到單一的結果集中。這在用戶看來是一個單獨的表，稱為分區視圖 。例如，如果一個表包含天津的銷售數據，另一個表包含北京的銷售數據，則可以對這兩個表使用UNION創建一個視圖。該視圖代表這兩個地區的銷售數據。438.2 使用視圖創建視圖若要創建視圖，必須獲得數據庫所有者授予創建視圖的權限 。在創建視圖前需考慮如下規則 ：只能

25、在當前數據庫中創建視圖 。視圖名稱必須遵循標識符的規則，且對每個架構都必須唯一 ，該名稱不得與該架構包含的任何表的名稱相同。可以對其他視圖創建視圖 。不能將規則或DEFAULT定義與視圖相關聯 。不能將AFTER觸發器與視圖相關聯，只有INSTEAD OF觸發器可以與之相關聯 。448.2 使用視圖定義視圖的查詢不能包含COMPUTE子句、COMPUTE BY子句或INTO關鍵字 。定義視圖的查詢不能包含ORDER BY子句，除非在SELECT語句的選擇列表中還有一個TOP子句。 定義視圖的查詢不能包含指定查詢提示的OPTION子句 。不能創建臨時視圖，也不能對臨時表創建視圖 。下列情況下必須

26、指定視圖中每列的名稱 視圖中的任何列都是從算術表達式、內置函數或常量派生而來 ；視圖中有兩列或多列應具有相同名稱 ；希望為視圖中的列指定一個與其源列不同的名稱(也可以在視圖中重命名列) 。無論重命名與否，視圖列都會繼承其源列的數據類型 。458.2 使用視圖1. 使用Transact-SQL命令創建視圖 可以使用CREATE VIEW命令創建視圖，語句語法格式如下 ：CREATE VIEW schema_name . view_name (column ,.n ) AS select_statement WITH CHECK OPTION ; 468.2 使用視圖 view_name：視圖的名

27、稱 。 column ,.n ：視圖中的列使用的名稱 。 AS：指定視圖要執行的操作 。 select_statement：定義視圖的SELECT語句 。該語句可以使用多個表和其他視圖 。 WITH CHECK OPTION：強制針對視圖執行的所有數據修改語句都必須符合在select_statement中設置的條件 。通過視圖修改行時，WITH CHECK OPTION可確保提交修改后，仍可通過視圖看到數據。如果在select_statement中的任何位置使用TOP，則不能指定CHECK OPTION。478.2 使用視圖例如：使用CREATE VIEW命令。以下示例使用簡單SELECT語句

28、創建視圖 ，此視圖的數據來自teaching數據庫的student和s_c表。這些數據提供有關學生的學號、姓名以及所選課程的課號和成績 。USE teachingGOCREATE VIEW student_viewAS SELECT s.SNO, s.SNAME, sc.CNO, sc.GRADEFROM student s JOIN s_c sc on s.SNO=sc.SNOGO488.2 使用視圖2. 使用圖形工具創建視圖具體步驟詳見教材498.2 使用視圖 修改視圖視圖定義后，可以更改視圖的名稱或視圖的定義而無需刪除并重新創建視圖。刪除并重新創建視圖會造成與該視圖關聯的權限丟失。1.

29、重命名視圖在重命名視圖時，需考慮以下原則。要重命名的視圖必須位于當前數據庫中。新名稱必須遵守標識符規則。僅可以重命名具有更改權限的視圖。數據庫所有者可以更改任何用戶視圖的名稱 。508.2 使用視圖(1) 使用系統存儲過程重命名視圖可以使用系統存儲過程sp_rename來重命名視圖名稱，與重命名索引方法相同 。(2)使用圖形工具重命名視圖右擊要重命名的視圖，選擇“重命名”，然后輸入新視圖名稱，回車即可 。注意：重命名視圖并不更改它在視圖定義文本中的名稱。要在定義中更改視圖名稱，應直接修改視圖。518.2 使用視圖2. 修改視圖定義修改先前創建的視圖，其中包括索引視圖 。(1) 使用Transa

30、ct-SQL命令修改視圖 可以使用ALTER VIEW命令修改視圖定義，該命令不影響相關的存儲過程或觸發器，并且不會更改權限 。語法格式如下 ：ALTER VIEW schema_name . view_name ( column ,.n ) AS select_statement WITH CHECK OPTION ; 528.2 使用視圖注意：如果原來的視圖定義是使用WITH CHECK OPTION創建的，則只有在ALTER VIEW中也包含這些選項時，才會啟用這些選項 。例如：修改teaching數據庫中的視圖student_view，使其只包含女學生的選課信息。 USE teachi

31、ngGOALTER VIEW student_viewAS SELECT s.SNO, s.SNAME, sc.CNO, sc.GRADEFROM student s JOIN s_c sc on s.SNO=sc.SNOWHERE s.SEX=FGO(2) 使用圖形工具修改視圖具體步驟詳見教材538.2 使用視圖3. 通過視圖修改數據可以通過視圖修改基礎表的數據，修改方式與通過UPDATE、INSERT和DELETE命令修改表中數據的方式一樣 。但是，以下限制應用于更新視圖，但不應用于更新表 。任何修改(包括UPDATE、INSERT和DELETE語句)都只能引用一個基礎表的列 。視圖中被修

32、改的列必須直接引用表列中的基礎數據，不能通過其他方式派生 。正在修改的列不受GROUP BY、HAVING或DISTINCT子句的影響。548.2 使用視圖如果在視圖定義中使用了WITH CHECK OPTION子句，則所有在視圖上執行的數據修改語句都必須符合定義視圖的SELECT語句中所設置的條件。如果使用了WITH CHECK OPTION子句，修改行時需注意不讓它們在修改完成后從視圖中消失 。INSERT語句必須為不允許空值并且沒有DEFAULT定義的基礎表中的所有列指定值。在基礎表的列中修改的數據必須符合對這些列的約束，例如為空性、約束及DEFAULT定義等。 558.2 使用視圖(1

33、) 通過視圖添加數據 例如：通過視圖將數據加載到基礎表。以下示例在INSERT命令中指定一個視圖名，但執行后系統將新行插入到該視圖的基礎表中。INSERT語句中VALUES列表的順序必須與視圖的列順序相匹配 。568.2 使用視圖USE teachingGOCREATE VIEW stu_male_view AS SELECT SNO, SNAME, AGEFROM studentWHERE SEX=MGOINSERT INTO stu_male_view VALUES (S6, HUANG, 21)GOSELECT * FROM studentGOSELECT * FROM stu_male

34、_viewGO對視圖stu_male_view增加的數據反映到基礎表student中，即：INSERT INTO stu_male_view VALUES (S6, HUANG, 21)語句轉變為：INSERT INTO student VALUES (S6, HUANG, M, 21)578.2 使用視圖(2) 通過視圖更改數據例如：通過視圖將數據修改到基礎表。以下示例在UPDATE語句中指定一個視圖名，但將修改的數據反映到該視圖的基礎表中 。USE teachingGOCREATE VIEW stu_male_viewAS SELECT SNO, SNAME, AGEFROM student588.2 使用視圖WHERE SEX=MGOUPDATE stu_male_view SET AGE=22 WHERE SNO=S2GOSELECT * FROM studentGOSELECT * FROM stu_male_viewGO對視圖stu_male_view增加的數據反映到基礎表student中，即：UPDATE stu_male_view SET