Index- 善用索引

1. Index- 善用索引

2. EX: 圖書館的索書編號 • 索引的結構 • LIKEBalance Tree What is “Index”

3. Clustered v.s. Non-clustered • Clustered 叢集索引&非叢集索引

4. 叢集索引&非叢集索引 cont. • Clustered v.s. Non-clustered • Non-Clustered

5. In Clustered &Non-clustered • 唯一索引(Unique index) • 任何兩筆紀錄的索引值不可相同 • 最常用在Primary Key的欄位上 • 建議同時NOT NULL Unique

6. 兩個或多個欄位組合成為索引(Composite index) 若同時是唯一索引，則不可重複的是? Composite

7. CREATE TABLE TABLE_1 ( ProductIDsmallint NOT NULL, ProductName char(30) UNIQUE, Price smallmoney, Manufacturer char(30) ) 自動設立非索引叢集的唯一索引

8. 包含的資料行 • 包含索引外的其他欄位 • 忽略重複的索引欄位 • 無論是否都拒絕重複值 • 重新計算統計資料 • 索引變動時是否自動更新 • 索引頁預留空間 • 填滿因數 • 末節點所使用的空間百分比 屬性表

9. CREATE TABLE TABLE_2 ( ProductIDsmallint NOT NULL Primary Key, ProductName char(30), Price smallmoney, Manufacturer char(30) ) Primary KeyDemo

10. 一個資料表只能有1個叢集索引 • 可組合多個欄位 • 索引最多使用十六個欄位 • 總長度需在900 byte之內 • 一個資料表最多249個非叢集索引 • 內容少時不建議使用primary key 以外的索引，會浪費時間空間成本 • 同質性高的欄位不適合成為索引 • EX:性別 建立索引的注意事項

11. CREATE INDEX Index_name ON table_name (column1,…..) 建立索引 語法

12. CREATE TABLE TABLE_3 ( c1 int NOT NULL Primary key, c2 char(4), c3 char(6), c4 char(30) ) --------------------------------- CREATE INDEX MyIndex_1 ON Table_3 (c1) CREATE INDEX MyIndex_2 ON Table_3 (c2, c3) 建立索引 語法

13. CREATE [Unique] --指定唯一 [CLUSTERED| NONCLUSTERED]--叢集與否 INDEX Index_name ONtable_name (column1[ASC|DESC][,….. n]) --指定升降冪 [Include(column[…,n])]--將其他資料行包含於此索引內 [WITH [PAD_INDEX] --索引頁面預留空間 [,FillFactor = x]–填滿因數 [,Ignore_DUP_Key] –忽略重複值 [,Drop_Existing] – 忽略現成的索引 [,Statistics_NoRecompute] --不重新計算統計值 [ON FileGroup] – 儲存的檔案群組 進階建立索引

14. CREATE TABLE TABLE_4 ( ProductIDsmallint NOT NULL Primary Key, ProductName char(30), Price smallmoney, Manufacturer char(30) ) • CREATE UNIQUENONCLUSTERED INDEX index_3 • ON TABLE_4 (ProductName) • INCLUDE (price) • WITH PAD_INDEX, FILLFACTOR=30, IGNORE_DUP_KEY • EXEC sp_helpindex table_4 進階建立索引

15. DROP INDEX Table_Name.index_name DROP INDEX Table_4.index_3 刪除索引

16. CREATE TABLE MyTable ( ProductIDsmallint NOT NULL Primary key, ProductName char(30) UNIQUE, Price smallmoney, Manufacturer char(30) ) EXEC sp_helpindexMyTable ---A DROP INDEX MyTable.PK__MyTable__7D439ABD DROP INDEX MyTable.UQ__MyTable__7E37BEF6 --- B ALTER TABLE MyTable DROP CONSTRAINT PK__MyTable__7D439ABD ALTER TABLE MyTable DROP CONSTRAINT UQ__MyTable__7E37BEF6 刪除索引 cont.

17. DROP_EXISTING =>代替原來已存在的索引 (建議使用) 維持原來索引的名稱，針對要改的細項做更改即可 修改或重建索引

18. DBCC DBREINDEX(‘DataBaseName.Schema.table_name’,Index_Name, FillFactor) [WITH NO_INFOMSGS] -- 不顯示訊息 EX: DBCC DBREINDEX(客戶, PK_客戶,70) 重建索引

19. SET ARITHABORT, CONCAT_NULL_YIELDS_NULL, • QUOTED_IDENTIFIER, ANSI_NULLS, • ANSI_PADDING, ANSI_WARNINGS ON • SET NUMERIC_ROUNDABORT OFF • GO • CREATE VIEWdbo.產品日報 • WITH SCHEMABINDING • AS • SELECT 下單日期 AS 日期, 書籍編號 AS 書號, • SUM (數量) AS 每日銷售量, COUNT_BIG (*) AS 每日訂單數 • FROM dbo.訂單 INNER JOIN dbo.訂購項目 • ON 訂單.訂單編號 = 訂購項目.訂單編號 • GROUP BY 下單日期, 書籍編號 • GO • SELECT * FROM 產品日報 替檢視表建立索引

20. CREATE UNIQUE CLUSTERED INDEX PK_產品日報 ON 產品日報 (日期, 書號) 替檢視表建立唯一性叢集索引

21. CREATE INDEX IX_書號 ON 產品日報(書號) INCLUDE (日期, 每日銷售量) 建立非叢集索引

22. ---使用主索引查詢 SELECT 日期, 書號, 每日銷售量, 每日訂單數 FROM 產品日報 WHERE 日期 = '2005/9/11' ORDER BY 日期 ---直接在VIEW以書號抓資料 SELECT 日期, 書號, 每日銷售量 FROM 產品日報 WHERE 書號 = 2 ---直接在VIEW以PK去抓資料 SELECT 下單日期, SUM(數量) AS 銷售量 FROM 訂單 INNER JOIN 訂購項目 ON 訂單.訂單編號 = 訂購項目.訂單編號 GROUP BY 下單日期, 書籍編號 ORDER BY 下單日期 索引查詢範例

23. 當刪除了檢視表中的主索引時，所有的非叢集索引會一併刪除!當刪除了檢視表中的主索引時，所有的非叢集索引會一併刪除! 刪除檢視表的索引