12 規劃與建立索引 12-1 索引的基礎 12-2 資料表的索引規劃 12-3 SQL Server自動建立的索引

Slides:



Advertisements
Similar presentations
第二章 简单的 SQL 语句. 本章要点  创建一个简单的表  SQL 语句介绍  最简单的查询语句 select  带有限制条件的查询  查询结果的排序显示.
Advertisements

第 22 章 SQL Server與XML.
第5章 关系数据库标准语言SQL 主讲:张丽芳.
第2讲 Transact-SQL语言.
第5章 索引及视图操作 数据库原理应用与实践 SQL SERver2014(第2版) 主编 贾铁军 科学出版社 编著 陈国秦 万程 邢一鸣
第8章 SELECT敘述的基本查詢 8-1 SELECT查詢指令 8-2 SELECT子句 8-3 FROM子句 8-4 WHERE子句
第4章 关系数据库标准语言SQL 4.1 SQL语言概述 4.2 SQL数据查询功能 4.3 SQL数据操作功能 4.4 SQL数据定义功能.
第3章 SQL的基础知识 数据库管理及应用 3.1 SQL简介 3.2 SQL的数据模型 3.3 标识符 3.4 使用SQL语句管理数据库
计算机应用基础 上海大学计算中心.
Views ,Stored Procedures, User-defined Function, Triggers
第7章 建立資料表與完整性限制條件 7-1 資料型別 7-2 資料表的建立 7-3 建立完整性限制條件 7-4 修改與刪除資料表
SQL Structured Query Language 用以操縱資料庫的指令集 資料操作語言DML:操作資料錄
Excel資料庫分析 台灣微軟資深講師 王作桓.
9 SELECT敘述的進階查詢 9-1 SQL的多資料表查詢 9-2 合併查詢 9-3 集合運算查詢 9-4 子查詢
14 預存程序與順序物件 14-1 預存程序的基礎 14-2 建立與執行預存程序 14-3 預存程序的參數傳遞 14-4 預存程序的傳回值
LINQ 建國科技大學 資管系 饒瑞佶.
課程名稱:資料庫系統 授課老師:李春雄 博士
9/28號專題報告 Web網頁遊戲 曾建瑋.
PHP與MySQL 入門學習指南 凱文瑞克 著 第 24章 建立資料表與索引.
連結資料庫 ACCESS MSSQL.
Chap 10 SQL定義、操作與控制指令.
第 11 章 建立檢視表.
SQL Server 2005 Query Optimizer 善用索引加快查詢效率 (參考實習課課本第 13 章)
高等資料庫管理系統 Advanced Database Management System
第八章 利用SELECT查詢資料.
第 7 章 建立資料表與 資料庫圖表.
教 师:曾晓东 电 话: 数据库技术 教 师:曾晓东 电 话:
SQL語法 定義與操作指令.
第10章 SQL定義、操作與控制指令 10-1 SQL語言的基礎 10-2 SQL查詢工具 10-3 資料庫的實體資料模型
SQL Stored Procedure SQL 預存程序.
第七章: 建立資料表.
SQL語法.
連結資料庫管理系統.
資料庫程式設計 VB資料庫設計簡介 週次:6 建國科技大學 資管系 饒瑞佶.
Ch4.SQL Server 2005資料庫組成員元件介紹
iRepor报表设计基础 IReport安装 普通实体报表 数据结果集报表 工作流主从报表 饼状图报表 柱状图,曲线图报表 条形码报表
Chap3 Linked List 鏈結串列.
Chapter 11 B-tree 11.1 m-way 搜尋樹 11.2 B-tree.
20 SQL Server全文檢索搜尋 20-1 SQL Server全文檢索搜尋 20-2 全文檢索目錄的建立 20-3 建立全文檢索索引
第 19 章 XML記憶體執行模式.
第18章 SQL結構化查詢語言 18-1 SQL語言的基礎 18-2 SQL的查詢指令 18-3 SQL子查詢與合併查詢.
FILESTREAM、FileTable、JSON與R語言
3. SQL语言的应用 3.1 SQL历史和优点 3.2 数据查询 3.3 数据操纵.
第14章 SQL数据查询与操纵 内容提要 本章知识点
SQL查询语句 蔡海洋.
16 觸發程序 16-1 觸發程序的基礎 16-2 DML觸發程序 16-3 修改、停用與刪除DML觸發程序 16-4 DDL觸發程序.
讲课人:王璞 浙江工商职业技术学院.
第4章 表的创建与维护 4.1 数据类型 4.2 数据完整性约束 4.3 创建数据表 4.4 修改数据表 4.5 删除数据表.
MicroSim pspice.
8 SELECT敘述的基本查詢 8-1 SELECT查詢指令 8-2 SELECT子句 8-3 FROM子句 8-4 WHERE子句
第11章 SQL基本查詢指令 11-1 SELECT查詢指令 11-2 WHERE子句的比較運算子 11-3 WHERE子句的邏輯運算子
SQL Server 2005 Query Optimizer 善用索引加快查詢效率 (參考實習課課本第 13 章)
MiRanda Java Interface v1.0的使用方法
陣列與結構.
SQL語法教學 2015/10/15 John.
第 4 章 認識 SQL 語言與資料型別.
動畫演示 Node規範了一些基本的方法,像是增加節點、刪除節點、讓節點做一些事、取得第n個節點等等
資料表示方法 資料儲存單位.
11 檢視表的建立 11-1 檢視表的基礎 11-2 建立檢視表 11-3 修改與刪除檢視表 11-4 編輯檢視表的內容.
資料庫系統_答案 Database System Week3
資料結構與C++程式設計進階 期末考 講師:林業峻 CSIE, NTU 7/ 15, 2010.
Cloud Training Material- 事件 Sherman Wang
網路上免費使用的Medline PubMed-Medline.
String類別 在C語言中提供兩種支援字串的方式 可以使用傳統以null結尾的字元陣列 使用string類別
第4章 数据查询.
SQLite資料庫 靜宜大學資管系 楊子青.
10303: How Many Trees? ★★☆☆☆ 題組:Contest Archive with Online Judge
Joining Multiple Tables
第 9 章 查詢資料- 善用 SELECT 敘述.
Develop and Build Drives by Visual C++ IDE
Presentation transcript:

12 規劃與建立索引 12-1 索引的基礎 12-2 資料表的索引規劃 12-3 SQL Server自動建立的索引 12-4 建立資料表的索引 12-5 修改、重建與刪除索引 12-6 檢視SQL Server的執行計劃 12-7 建立檢視表與計算欄位的索引 12-8 篩選索引與資料行存放區索引

12-1 索引的基礎 12-1-1 索引簡介 12-1-2 索引的種類 12-1-3 M路搜尋樹與B樹 12-1-4 SQL Server的索引結構

12-1-1 索引簡介-說明 索引(Index)可以幫助資料庫引擎在磁碟中定位記錄資料,以便在資料表的龐大資料中加速找到資料。換句話說,建立資料表的索引可以提昇SQL查詢效率,讓我們更快取得資料庫的查詢結果。 在資料表建立索引需要額外的參考資料,資料庫管理系統可以將資料表的部分欄位資料預先進行排序,此欄位稱為「索引欄位」(Index Columns),索引欄位值稱為鍵值(Key Value)。

12-1-1 索引簡介-圖例 一般來說,索引資料包含兩個欄位值:一為索引欄位;一為指標(Pointer)欄位,它是指向對應到資料表記錄位置的值,如下圖所示:

12-1-2 索引的種類-主索引 主索引(Primary Index)就是將資料表的主索引鍵建立成索引,一個資料表只能擁有一個主索引。在資料表建立主索引的索引欄位,欄位值一定不能重覆,即欄位值是唯一,而且不允許是空值(NULL)。 在主索引的索引欄位可以是一個或多個欄位的組合,如果是由多個資料表欄位所組合,稱為複合索引(Composite Index)或結合索引(Concatenated Index),在主索引的複合索引中,個別欄位允許重複值,但是整個組合值仍然需要是唯一值。

12-1-2 索引的種類 唯一索引(Unique Index)的欄位值也是唯一的,不同於主索引只能有一個,在一個資料表可以擁有多個唯一索引,這也是與主索引唯一的差別。 一般索引(Regular Index)的索引欄位值並不需要是唯一的,其主要目的是加速資料表的搜尋與排序。在一個資料表可以擁有多個一般索引。換句話說,我們可以在資料表選擇一些欄位來建立一般索引,其主要目的就是在增進查詢效能。

12-1-3 M路搜尋樹與B樹-說明 B樹(B-Trees)是資料結構的一種樹狀搜尋結構,它是擴充自二元搜尋樹的一種平衡的M路搜尋樹。 M路搜尋樹(M-way Search Trees)是指樹的每一個節點都擁有至多M個子樹和M-1個鍵值,鍵值是以遞增方式由小至大來排序,其節點結構如下圖所示:

12-1-3 M路搜尋樹與B樹- M路搜尋樹(圖例) 例如:四路搜尋樹的每一個節點最多有3鍵值和4個子樹,如下圖所示:

12-1-3 M路搜尋樹與B樹-B樹 (說明) B樹(B-Tree)屬於一種樹狀搜尋結構,它是擴充自二元搜尋樹的一種平衡的M路搜尋樹。M為B樹的度數(Order),由Bayer和McCreight提出的一種平衡的M路搜尋樹,其定義如下所示: B樹的每一個節點最多擁有M個子樹。 B樹根節點和葉節點之外的中間節點,至少擁有ceil(M/2)個子節點,ceil()函數可以大於等於參數的最小整數,例如:ceil(4) = 4、ceil(4.33) = 5、ceil(1.89) = 2和ceil(5.01) = 6。 B樹的根節點可以少於2個子節點。葉節點至少擁有ceil(M/2) - 1個鍵值。 B樹的所有葉節點都位在樹最底層的同一階層(Level),換句話說,從根節點開始走訪到各葉節點所經過的節點數都相同,它是一棵相當平衡的樹狀搜尋結構。

12-1-3 M路搜尋樹與B樹-B樹 (圖例) 例如:一棵度數5的B樹,所有中間節點至少擁有ceil(5/2) = 3個子節點(即至少2個鍵值),最多5個子節點(4個鍵值),葉節點至少擁有2個鍵值,最多為4個鍵值,如下圖所示:

12-1-4 SQL Server的索引結構-說明 SQL Server索引結構(Index Organization)是組成索引分頁的方法,可以分為叢集索引和非叢集索引兩種。 在SQL Server資料表只能擁有一個叢集索引,通常就是主索引,主索引的索引欄位可以是單一欄位,或多欄位的複合索引。 在一個資料表可以擁有多個非叢集索引,它可以是唯一索引或一般索引,當然也可以是多索引欄位的複合索引。

12-1-4 SQL Server的索引結構- 叢集索引 叢集索引(Clustered Indexes)是一種B樹結構,當SQL Server資料表建立叢集索引後,資料表的記錄資料會依叢集索引欄位的鍵值來排序,如下圖所示:

12-1-4 SQL Server的索引結構- 非叢集索引(說明) 非叢集索引(Nonclustered Indexes)是一種類似叢集索引的B樹結構,其差異在於資料表的記錄並不會依據非叢集索引的鍵值來排序,而且非叢集索引的葉節點是索引分頁,並不是資料分頁。 非叢集索引葉節點的索引分頁內容是非叢集索引鍵值,和指向資料表記錄的記錄定位(Row Locator)指標。在叢集資料表建立非叢集索引,因為資料表本身已經擁有叢集索引,所以葉節點的索引分頁中,記錄定位值是對應的叢集索引鍵值。

12-1-4 SQL Server的索引結構- 非叢集索引(圖例)

12-2 資料表的索引規劃 12-2-1 索引的優缺點 12-2-2 建立索引的注意事項 12-2-3 選擇索引欄位

12-2-1 索引的優缺點 索引的優點:索引可以加速資料存取,因為不用一筆一筆比較來搜尋記錄,資料庫引擎可以透過索引結構來快速找到指定記錄,它能夠讓SQL語言的合併查詢、排序和群組操作更加的有效率。 索引的缺點:在資料表建立索引需要額外的磁碟空間和維護成本,因為資料表在插入、更新和刪除記錄時,資料庫引擎需要花費額外時間和資源來更新索引資料。

12-2-2 建立索引的注意事項- 建立索引的限制條件 因為資料表的記錄資料是使用叢集索引的順序來排列,所以SQL Server資料庫的每一個資料表只能建立一個叢集索引,但是可以在資料表的多個欄位建立多個非叢集索引。 在一個資料表最多只能有一個叢集索引和249個非叢集索引。 複合索引欄位數最多只能有16個欄位。 單一索引欄位或複合索引欄位的總長度需在900位元組以內,而且不能替ntext、text和image資料類型的欄位建立索引。

12-2-2 建立索引的注意事項- 如何建立複合索引 複合索引是指索引欄位超過一個的索引,我們可以選擇資料表的多個欄位集合來建立複合索引。一般來說,在資料表應該儘量避免建立複合索引,而是以多個單一欄位索引來取代,因為複合索引的索引欄位尺寸通常比較大,需要更多的磁碟讀取,進而影響整體的執行效能。 不只如此,SQL Server複合索引在使用上有一些限制,只有當SELECT指令的WHERE子句使用第1個欄位進行查詢時,才會使用複合索引來增加查詢效率。

12-2-3 選擇索引欄位- 應該作為索引的欄位 對於資料表中查詢頻繁的欄位,我們應該替這些欄位建立索引,例如:主鍵、外來鍵、經常需要合併查詢的欄位、排序欄位和需要查詢指定範圍的欄位。 一般來說,資料表的主鍵建議建立叢集索引(SQL Server預設會自動建立),其他欄位建立成非叢集索引。

12-2-3 選擇索引欄位- 不應該作為索引的欄位 對於資料表查詢時很少參考到的欄位、大量重複值欄位(例如:欄位值只有男或女)或bit等資料類型的欄位,就不應該替它們建立索引。

12-3 SQL Server自動建立的索引 12-3-1 PRIMARY KEY欄位的索引 12-3-2 UNIQUE欄位的索引

12-3 SQL Server自動建立的索引 當在SQL Server資料庫建立資料表時,資料表指定為PRIMARY KEY或UNIQUE的欄位,SQL Server都會自動替這些欄位建立索引。

12-3-1 PRIMARY KEY欄位的索引 在建立資料表時指定為PRIMARY KEY的欄位(即主索引鍵或稱為主鍵),SQL Server預設將它自動建立成叢集索引,換句話說,資料表的記錄是使用主索引鍵欄位值來排列。 例如:【學生】資料表指定【學號】欄位的主索引鍵,預設就會建立此欄位的叢集索引。

12-3-2 UNIQUE欄位的索引-說明 在建立資料表時指定為UNIQUE的欄位,SQL Server都會預設自動建立成非叢集索引,其型別是唯一索引鍵,表示欄位值需要是唯一。

12-3-2 UNIQUE欄位的索引-範例 SQL指令碼檔:Ch12_3_2.sql CREATE TABLE 熱銷產品 ( 產品編號 char(5) NOT NULL PRIMARY KEY , 產品名稱 varchar(30) UNIQUE , 定價 money )

12-4 建立資料表的索引 12-4-1 使用Management Studio建立索引 12-4-2 使用T-SQL指令建立索引

12-4-1 使用Management Studio建立 索引-選擇索引欄位

12-4-1 使用Management Studio建立 索引-選擇內含欄位 接著我們可以加入索引包含的欄位,請選上方【包含的資料行】標籤,按【加入】鈕新增索引包含的欄位,可以看到勾選資料表欄位的對話方塊。如下圖所示:

12-4-1 使用Management Studio建立 索引-內含欄位說明 在資料表建立非叢集索引時,建議新增內含欄位,以便讓SQL Server能夠最佳化索引的使用。例如:查詢學生生日的SELECT指令,如下所示: SELECT 生日 FROM 學生 WHERE 姓名 = '陳會安' 上述SELECT指令可以使用【姓名_索引】的非叢集索引,因為有在索引包含【生日】欄位。 如果沒有包含【生日】欄位且沒有【生日】欄位的索引,SQL Server仍然會使用主索引的叢集索引來進行搜尋,而不會使用【姓名_索引】的非叢集索引。

12-4-2 使用T-SQL指令建立索引-語法 在T-SQL語言是使用CREATE INDEX指令建立資料表的索引,其基本語法如下所示: CREATE [ UNIQUE ] [ CLUSTERED ] INDEX 索引名稱 ON 資料表名稱 (欄位名稱[(長度)][ ASC | DESC ][,..n] ) [ INCLUDE (欄位清單) ] [ WITH 索引選項 ] [ ON filegroup_name ]

12-4-2 使用T-SQL指令建立索引-索引選項 在WITH子句可以指定索引選項,如果有多個,請使用「,」逗號分隔。常用的索引選項說明,如下表所示: 索引選項 說明 PAD_INDEX 索引頁預留空間 FILLFACTOR = x 填滿因數 IGNORE_DUP_KEY 忽略重複值 STATISTICS_NORECOMPUTE 不重新計算統計資料 DROP_EXISTING 重建存在的索引,即卸除目前的索引後,重新建立

12-4-2 使用T-SQL指令建立索引-範例1 SQL指令碼檔:Ch12_4_2_01.sql 在【教務系統】資料庫的【員工】資料表新增【姓名】欄位的非叢集索引【員工姓名_索引】,索引包含【電話】和【薪水】欄位,如下所示: CREATE INDEX 員工姓名_索引 ON 員工(姓名) INCLUDE (電話, 薪水)

12-4-2 使用T-SQL指令建立索引-範例2 SQL指令碼檔:Ch12_4_2_02.sql 在【教務系統】資料庫的【課程】資料表新增【名稱】和【學分】欄位的非叢集索引【名稱學分_索引】,這是一個唯一的複合索引,如下所示: CREATE UNIQUE INDEX 名稱學分_索引 ON 課程(名稱, 學分)

12-5 修改、重建與刪除索引 12-5-1 使用Management Studio修改與重建資料表的索引 12-5-2 使用T-SQL指令修改與重建索引 12-5-3 刪除資料表的索引

12-5-1 使用Management Studio修改與 重建資料表的索引-方法1 在「物件總管」視窗【員工姓名_索引】上,執行【右】鍵快顯功能表的【屬性】指令,可以看到「索引屬性」對話方塊來修改索引。

12-5-1 使用Management Studio修改與 重建資料表的索引-方法2 在修改資料表欄位定義資料時,執行「資料表設計工具>索引/索引鍵」指令,開啟「索引/索引鍵」對話方塊來修改索引,如下圖所示:

12-5-1 使用Management Studio修改與 重建資料表的索引-重建索引

12-5-2 使用T-SQL指令修改與重建索引- 修改索引(說明) T-SQL語言修改索引仍然是使用CREATE INDEX指令,只是加上DROP_EXISTING選項。 在CREATE INDEX指令建立索引時,如果加上DROP_EXISTING選項,表示我們準備建立新索引來取代同名的存在索引。請注意!當加上DROP_EXISTING選項時,一定需要存在同名的索引,如果索引不存在,執行時就會產生錯誤。

12-5-2 使用T-SQL指令修改與重建索引- 修改索引(範例) SQL指令碼檔:Ch12_5_2_01.sql 在【教務系統】資料庫的【員工】資料表修改【員工姓名_索引】索引,將它改為唯一索引、加上IGNORE_DUP_KEY選項和新增包含【城市】欄位,如下所示: CREATE UNIQUE INDEX 員工姓名_索引 ON 員工(姓名) INCLUDE (電話, 薪水, 城市) WITH IGNORE_DUP_KEY, DROP_EXISTING

12-5-2 使用T-SQL指令修改與重建索引- 重建索引(語法) T-SQL的ALTER INDEX指令可以停用、重建索引、重組索引或設定索引選項來修改存在的索引,其基本語法如下所示: ALTER INDEX 索引名稱 [ALL] ON 資料表名稱 [ REBUILD [WITH (索引選項清單)] | REORGANIZE | DISABLE | SET (索引選項清單)]

12-5-2 使用T-SQL指令修改與重建索引- 重建索引(範例1) SQL指令碼檔:Ch12_5_2_02.sql 重建【教務系統】資料庫【員工】資料表的所有索引,並且將填滿因數改為80%,如下所示: ALTER INDEX ALL ON 員工 REBUILD WITH (FILLFACTOR = 80)

12-5-2 使用T-SQL指令修改與重建索引- 重建索引(範例2) SQL指令碼檔:Ch12_5_2_03.sql 在【教務系統】資料庫停用【員工】資料表的【員工姓名_索引】索引,如下所示: ALTER INDEX 員工姓名_索引 ON 員工 DISABLE

12-5-3 刪除資料表的索引- 使用Management Studio 我們也可以在「索引/索引鍵」對話方塊刪除索引,請在左邊選取索引後,按下方的【刪除】鈕來刪除資料表的索引。

12-5-3 刪除資料表的索引-使用T-SQL指令 T-SQL語言的DROP INDEX指令可以刪除資料表的索引,其基本語法如下所示: DROP INDEX 資料表名稱1.索引名稱1 [, 資料表名稱2.索引名稱2, …] SQL指令碼檔:Ch12_5_3.sql 在【教務系統】資料庫刪除【員工】資料表的【員工姓名_索引】索引,如下所示: DROP INDEX 員工.員工姓名_索引

12-6 檢視SQL Server的執行計劃-說明 在Management Studio工具可以使用圖形化方式來顯示SQL Server執行計劃,執行計劃是SQL Server查詢最佳化模組選擇的資料擷取方法,我們可以透過檢視執行計劃來了解查詢特性,並且幫助我們進行查詢最佳化。 Management Studio的估計執行計劃並不會真的執行T-SQL查詢或批次。不過,此估計執行計劃仍有可能是資料庫引擎最後使用的執行計劃。

12-6 檢視SQL Server的執行計劃-圖例

12-7 建立檢視表與計算欄位的索引 12-7-1 建立計算欄位的索引 12-7-2 建立檢視表索引

12-7-1 建立計算欄位的索引-說明 在資料表新增計算欄位就是為了建立計算欄位的索引,例如:查詢【估價單】資料表平均單價在100和200元之間的記錄資料,如下所示: SELECT 產品編號 FROM 估價單 WHERE (總價 / 數量) BETWEEN 100.00 AND 200.00 在WHERE子句條件因為擁有運算式,當【估價單】資料表的記錄資料十分龐大時,我們就需要在資料表新增計算欄位【平均單價】(即SQL指令碼:Ch7_4_1.sql),然後建立此計算欄位的索引來增進查詢效率。

12-7-1 建立計算欄位的索引-需求條件1 擁有權需求(Ownership Requirements):計算欄位與資料表必須是同一位擁有者,即計算欄位的運算式不能使用其他資料表的欄位。 決定性需求(Determinism Requirements):計算欄位的值是由運算式的欄位值決定,只需輸入的欄位值相同,就會輸出相同的結果。 精確性需求(Precision Requirements):計算欄位的運算式結果需要是精確的(Precise),也就是沒有使用float或real型別的欄位。

CONCAT_NULL_YIELDS_NULL 12-7-1 建立計算欄位的索引-需求條件2 資料類型需求(Data Type Requirements):計算欄位的運算式結果不可以是text、ntext和image型別,不過,組成欄位仍然可以使用這些型別。 SQL選項需求(SQL Option Requirements):在建立時需要使用SET指令指定一些SQL選項。 SQL選項 設定值 NUMERIC_ROUNDABORT OFF ANSI_NULLS ON ANSI_PADDING ANSI_WARNINGS ARITHABORT CONCAT_NULL_YIELDS_NULL QUOTED_IDENTIFIER

12-7-1 建立計算欄位的索引- 建立計算欄位的索引(範例1) SQL指令碼檔:Ch12_7_1_01.sql 在【教務系統】資料庫的【估價單】資料表,建立計算欄位【平均單價】的【平均單價_索引】索引,如下所示: SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON SET NUMERIC_ROUNDABORT OFF

12-7-1 建立計算欄位的索引- 建立計算欄位的索引(範例2) GO CREATE NONCLUSTERED INDEX 平均單價_索引 ON 估價單(平均單價 ASC) INCLUDE (產品編號)

12-7-2 建立檢視表索引-說明 檢視表索引(Indexed View)是指擁有唯一叢集索引的檢視表,在SQL Server不只可以替一般資料表建立索引,就連虛擬資料表的檢視表也可以建立索引,其最主要目的是增進檢視表的查詢效率,特別針對大量資料的合併和統計摘要檢視表,如下所示: 合併檢視表:多資料表執行大量資料的合併查詢。 統計摘要檢視表:使用群組和聚合函數建立大量資料的統計查詢。

12-7-2 建立檢視表索引- 檢視表索引的限制條件1 需要是檢視表的擁有者,才有權限執行CREATE INDEX指令建立檢視表的索引。 檢視表索引欄位需要是是精確的(Precise),也就是沒有使用float或real型別的欄位。 檢視表需要先建立唯一叢集索引後,才能建立其他非叢集索引。 如果建立檢視表的SELECT指令包含GROUP BY子句,在建立唯一叢集索引時,就只能選擇GROUP BY子句的欄位。

12-7-2 建立檢視表索引- 檢視表索引的限制條件2 和建立計算欄位的索引相同,在建立檢視表索引前,我們需要設定SQL選項:ANSI_NULLS、ANSI_PADDING、ANSI_WARNINGS、ARITHABORT、CONCAT_NULL_YIELDS_NULL、QUOTED_IDENTIFIER為ON,SET NUMERIC_ROUNDABORT選項為OFF。 在建立好唯一叢集索引後,對於基底資料表執行INSERT、UPDATE和DELETE指令或查詢時,都需要設定前述7個SQL選項。

12-7-2 建立檢視表索引- 基底資料表的限制條件 對於檢視表資料來源的基底資料表來說,在使用CREATE TABLE指令建立資料表時,SQL選項ANSI_NULLS需設為ON。

12-7-2 建立檢視表索引- 檢視表的限制條件1 在使用CREATE VIEW建立檢視表時,SQL選項ANSI_NULLS和QUOTED_IDENTIFIER需為ON。 建立檢視表時需要使用WITH SCHEMABINDING選項。 檢視表的資料來源只能是同一個資料庫的資料表,而不能是檢視表,而且它們是同一位擁有者。 在檢視表的所有欄位都需是決定性欄位,不能包含text、ntext和image型別。至於參考的資料表和自訂函數名稱都需使用二段式名稱,例如:dbo.員工,也就是包含結構描述。

12-7-2 建立檢視表索引- 檢視表的限制條件2 SELECT指令不可使用的指令,如下所示: SELECT子句不可以使用「*」,一定需要指明欄位名稱,而且不能使用TOP和DISTINCT關鍵字。 FROM子句不可使用子查詢的衍生資料表(Derived Tables)。也不能使用OUTER JOIN、UNION。 GROUP BY子句不可使用HAVING子句、CUBE和ROLLUP。 不可以使用ORDER BY和COMPUTE BY子句。 只能使用SUM()和COUNT_BIG()聚合函數(與COUNT()函數相同,只是傳回bigint資料類型),而且SUM()函數的欄位不能是NULL空值。 不能使用資料集函數,例如:OPENROWSET(),和不可以使用全文檢索搜尋的CONTAINS和FREETEXT。

12-7-2 建立檢視表索引- 建立檢視表(範例1) SQL指令碼檔:Ch12_7_2_01.sql 在【教務系統】資料庫建立名為【學生上課教室_檢視】的合併檢視表,可以顯示學生在各教室的上課數,如下所示: SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON SET NUMERIC_ROUNDABORT OFF GO

12-7-2 建立檢視表索引- 建立檢視表(範例2) CREATE VIEW dbo.學生上課教室_檢視 WITH SCHEMABINDING AS SELECT 學生.學號, 班級.教室,COUNT_BIG(*) AS 上課數 FROM dbo.學生 INNER JOIN dbo.班級 ON 學生.學號 = 班級.學號 GROUP BY 學生.學號, 班級.教室 GO SELECT * FROM 學生上課教室_檢視

12-7-2 建立檢視表索引- 建立唯一叢集索引(範例) SQL指令碼檔:Ch12_7_2_02.sql 在【教務系統】資料庫替【學生上課教室_檢視】檢視表,建立名為【上課報表_索引】的唯一叢集索引,如下所示: CREATE UNIQUE CLUSTERED INDEX 上課報表_索引 ON 學生上課教室_檢視(學號, 教室)

12-7-2 建立檢視表索引- 建立非叢集索引(範例) SQL指令碼檔:Ch12_7_2_03.sql 在【教務系統】資料庫替【學生上課教室_檢視】檢視表,建立名為【教室_索引】的非叢集索引,如下所示: CREATE NONCLUSTERED INDEX 教室_索引 ON 學生上課教室_檢視(教室) INCLUDE (學號, 上課數)

12-7-2 建立檢視表索引- 檢視估計執行計劃 請在Management Studio分別開啟SQL指令碼檔案Ch12_7_2_04.sql和Ch12_7_2_05.sql後,當我們檢視估計執行計劃,即可看到查詢最佳化模組,使用檢視表索引來執行SELECT指令。

12-8 篩選索引與資料行存放區索引 12-8-1 篩選索引 12-8-2 資料行存放區索引 12-8-3 叢集資料行存放區索引

12-8-1 篩選索引-說明 篩選索引(Filtered Index)是一種擁有條件的索引,換句話說,SQL Server並不會將索引欄位的所有記錄都建立索引,而是只有哪些符合條件的記錄才會建立索引。 在CREATE INDEX指令是使用WHERE子句來指定建立篩選索引的篩選條件。實務上,篩選索引最常是使用在建立疏鬆欄位的索引。

12-8-1 篩選索引-範例1 SQL指令碼檔:Ch12_8_1.sql 在【教務系統】資料庫建立【廠商名單】資料表,內含疏鬆欄位【分公司數】,然後建立名為【分公司數_索引】的篩選索引,如下所示: CREATE TABLE 廠商名單 ( 廠商編號 int NOT NULL IDENTITY PRIMARY KEY, 廠商名稱 varchar(100), 廠商類型 tinyint NOT NULL, 分公司數 int SPARSE )

12-8-1 篩選索引-範例2 GO CREATE NONCLUSTERED INDEX 分公司數_索引 ON 廠商名單(分公司數) WHERE 廠商類型 = 3

12-8-2 資料行存放區索引-說明 「資料行存放區索引」(Columnstore Index,全名為非叢集資料行存放區索引)是針對大型資料倉儲新增以欄位為基礎(Column-based)的索引,基本上,資料行存放區索引不是使用紀錄為單位來儲存,而是改用欄位為單位來儲存,如下圖所示:

12-8-2 資料行存放區索引-提升查詢效率 因為資料行存放區索引是使用VertiPaq壓縮技術,可以在記憶體中儲存龐大的壓縮資料來減少I/O和搜尋時間。例如:以生日為條件的SQL查詢,如下所示: SELECT 姓名, 電話 FROM 學生 WHERE 生日='1997-09-03' 上述SELECT指令的WHERE子句是使用生日為條件,資料行存放區索引只需載入生日欄位的資料頁,不用載入整筆記錄的資料頁,因為資料量大幅減少,可以大幅提升資料查詢效率。

12-8-2 資料行存放區索引-注意事項 一個SQL Server資料表只能有一個資料行存放區索引。 資料行存放區索引不能使用篩選條件,也不能使用INCLUDE關鍵字。 資料行存放區索引的索引欄位不可是計算欄位。 資料行存放區索引的資料表成為唯讀資料表。 資料行存放區索引的索引欄位不支援binary、varbinary、ntext、text、image、varchar(max)、nvarchar(max)、uniqueidentifier、rowversion、timestamp、sql_variant、超過18位數的decimal和numeric資料類型。

12-8-2 資料行存放區索引-使用Management Studio建立 請在「物件總管」視窗展開資料表【課程備份】 ,在【索引】上執行【右】鍵快顯功能表的「新增索引>非叢集資料行存放區索引」指令,可以看到「新增索引」對話方塊。

12-8-2 資料行存放區索引- 使用T-SQL建立(語法) T-SQL語言建立資料行存放區索引的語法和非叢集索引相似,只是改為COLUMNSTORE關鍵字,其語法如下所示: CREATE COLUMNSTORE INDEX 索引名稱 ON 資料表名稱 (欄位名稱1,欄位名稱2,… 欄位名稱N) 上述語法使用COLUMNSTORE關鍵字建立資料行存放區索引,在括號中是建立索引的欄位清單。

12-8-2 資料行存放區索引- 使用T-SQL建立(範例) SQL指令碼檔:Ch12_8_2_02.sql 請在【學生備份】資料表建立名為【學生資料行_索引】的資料行存放區索引,索引欄位有姓名、生日和電話,如下所示: CREATE COLUMNSTORE INDEX 學生資料行_索引 ON 學生備份 (姓名, 生日, 電話)

12-8-3 叢集資料行存放區索引-說明 在第12-8-2節預設建立的是非叢集資料行存放區索引,這是使用在唯讀查詢,而且無法更新索引。 SQL Server也可以建立叢集資料行存放區索引,這是一種可以執行DML指令的資料行存放區索引,我們可以在沒有叢集索引、唯一、主鍵和外來鍵條件約束的資料表建立叢集資料行存放區索引。

12-8-3 叢集資料行存放區索引-使用Management Studio建立 請在「物件總管」視窗展開【課程備份2】資料表,在【索引】上執行【右】鍵快顯功能表的「新增索引>叢集資料行存放區索引」指令,可以看到「新增索引」對話方塊。

12-8-3 叢集資料行存放區索引-使用T-SQL指令建立(語法) T-SQL語言建立叢集資料行存放區索引的語法和叢集索引相似,只是加上COLUMNSTORE關鍵字,其語法如下所示: CREATE CLUSTERED COLUMNSTORE INDEX 索引名稱 ON 資料表名稱 上述語法在COLUMNSTORE關鍵字之前加上CLUSTERED,就可以建立叢集資料行存放區索引。如果沒有加上CLUSTERED,或使用NONCLUSTERED,就是建立第12-8-2節的非叢集資料行存放區索引,其語法如下所示: CREATE NONCLUSTERED COLUMNSTORE INDEX 索引名稱 ON 資料表名稱 (欄位名稱1,欄位名稱2,… 欄位名稱N)

12-8-3 叢集資料行存放區索引-使用T-SQL指令建立(範例) 請執行Ch12_8_3_01.sql建立【學生備份2】資料表,筆者準備在此資料表使用T-SQL指令來建立叢集資料行存放區索引。 SQL指令碼檔:Ch12_8_3_02.sql 請在【學生備份2】資料表建立名為【學生資料行_叢集索引】的叢集資料行存放區索引,如下所示: CREATE CLUSTERED COLUMNSTORE INDEX 學生資料行_叢集索引 ON 學生備份2

12-8-3 叢集資料行存放區索引-使用T-SQL指令建立(圖例) 在成功執行SQL指令後,我們可以在Management Studio的「物件總管」視窗看到建立的叢集資料行存放區索引,如下圖所示: