SQL SERVER 索引與視圖學習

標籤：des   style   blog   color   io   使用   ar   資料   sp   

   索引的優點。建立唯一性索引可以確保行資料的唯一性；可以大大提高資料的檢索速度；可以加速表與表之間的串連；在使用order by，group by之句時，可以減少查詢中分組和排序的時間。

   索引的缺點。建立和維護索引需要耗費時間；索引佔用物理空間；當對錶中的資料進行，刪除，更改時，索引也要動態維護。

建立索引：

 1 CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] 2 INDEX <index name> ON <table or view name>(<column name> [ASC|DESC][,...n]) 3 INCLUDE (<column name> [,...n]) 4 [ 5     WITH 6     [PAD_INDEX = {ON | OFF}] 7     [[,] FILLFACTOR = <fillfactor>] 8     [[,] IGNORE_DUR_KEY = {ON | OFF}] 9     [[,] DROP_EXISTING = {ON | OFF}]10     [[,] STATISTICS_NORECOMPUTE = {ON | OFF}]11     [[,] SORT_IN_TEMPDB = {ON | OFF}]12     [[,] ONLINE = {ON | OFF}]13     [[,] ALLOW_ROW_LOCKS = {ON | OFF}]14     [[,] ALLOW_PAGE_LOCKS = {ON | OFF}]15     [[,] MAXDOP = <maxinum degree of parallelism>16 ]17 [ON {<filegroup> | <partition scheme name> | DEFAULT}]

eg.

USE test1

GO

IF EXISTS(SELECT * FROM SYSINDEXES WHERE NAME=‘ceshi_index‘)

DROP INDEX ceshi_index

Create unique nonclustered index ceshi_index

ON orders(customerID)

With FILLFACTOR = 30

GO

 

修改索引

1 Alter index ceshi_index on ceshi Rebuild 2 With (FILLFACTOR=30,IGNORE_DUP_KEY=ON)

 

SQL SERVER 索引與視圖學習