前期準(zhǔn)備：

create table Employee (
            ID int not null primary key,
            Name nvarchar(4),
            Credit_Card_ID varbinary(max)); --- 小心這種數(shù)據(jù)類型。
go

說明：本表上的索引，都會(huì)在創(chuàng)建下一個(gè)索引前刪除。

創(chuàng)建聚集索引

方法 1、

ALTER TABLE table_name ADD CONSTRAINT cons_name priamry KEY(columnname ASC|DESC,[.....]) WITH (drop_existing = ON);
ALTER TABLE employee ADD CONSTRAINT pk_for_employee PRIMARY KEY CLUSTERED (id);
go 

這個(gè)是一種特別的方法，因?yàn)樵诙x主鍵的時(shí)候，會(huì)自動(dòng)添加索引，好在加的是聚集索引還是非聚集索引是我們?nèi)藶榭梢钥刂频摹?/p>

通過sp_helpindex 可以查看表中的索引

execute sp_helpindex @objname = 'Employee';
go

注意:這個(gè)索引是無法刪除的，不信！ 你去刪一下

drop index Employee.PK__Employee__3214EC277D95E615;
go

方法 2、

create clustered index ix_name on table_name(columnName ASC|DESC[,......]) with (drop_existing = on);
create clustered index ix_clu_for_employee_ID on Employee(ID);
go

查看創(chuàng)建的索引

創(chuàng)建復(fù)合索引

create index ix_com_Employee_IDName on Employee (ID,Name)with (drop_existing = on);

這樣就算是創(chuàng)建一個(gè)復(fù)合索引了，不過腳下的路很長(zhǎng)，我們看下一個(gè)復(fù)合索引的例句：

create index ix_com_Employee_IDCreditCardID on Employee(ID,Credit_Card_ID);

看到這句話，你先問一下自己它有沒有錯(cuò)！

可以發(fā)現(xiàn)它錯(cuò)了，varbinary是不可以建索引的。

創(chuàng)建覆蓋索引

create index index_name on table_Name (columnName ASC|DESC[,......]) include(column_Name_List)with (drop_existing = on);
create index ix_cov_Employee_ID_Name on Employee (ID) include(Name);
go

首先，覆蓋索引它只是非聚集索引的一種特別形式，下文說的非聚集索引不包涵覆蓋索引，當(dāng)然這個(gè)約定只適用于這一段話，這樣做的目的是為了說明各中的區(qū)別。

首先：

• 非聚集索引不包涵數(shù)據(jù)，通過它找到的只是文件中數(shù)據(jù)行的引用（表是堆的情況下）或是聚集索引的引用，SQL Server要通這個(gè)引用去找到相應(yīng)的數(shù)據(jù)行。

• 正因?yàn)榉蔷奂饕鼪]有數(shù)據(jù)，才引發(fā)第二次查找。

• 覆蓋索引就是把數(shù)據(jù)加到非聚集索引上，這樣就不需要第二次查找了。這是一種以空間換性能的方法。非聚集索引也是。只是做的沒有它這么出格。

創(chuàng)建唯一索引

create unique index index_name on table_name (column ASC|DESC[,.....])with (drop_existing = on);

正如我前面所說，在創(chuàng)建表上的索引前，我會(huì)刪除表上的所有索引，這里為什么我要再說一下呢！因?yàn)槲遗履阃?。二來這個(gè)例子用的到它。
目前表是一個(gè)空表，我給它加兩行數(shù)據(jù)。

insert into Employee(ID,Name) values(1,'AAA'),(1,'BBB');

這下我們?yōu)楸砑游ㄒ凰饕?它定義在ID這個(gè)列上

create unique index ix_uni_Employee_ID on Employee(ID);
go -- 可以想到因?yàn)镮D有重復(fù)，所以它創(chuàng)建不了。

結(jié)論 1、 如果在列上有重復(fù)值，就不可以在這個(gè)列上定義，唯一索引。

下面我們把表清空：truncate table Employee

接下來要做的就是先，創(chuàng)建唯一索引，再插入重復(fù)值。

create unique index ix_uni_Employee_ID on Employee(ID);
go

insert into Employee(ID,Name) values(1,'AAA'),(1,'BBB');
go

結(jié)論 2、
定義唯一索引后相應(yīng)的列上不可以插入重復(fù)值。

篩選索引

create index index_name on table_name(columName) where boolExpression;
create index ix_Employee_ID on Employee(ID) where ID>100 and ID< 200;
go

只對(duì)熱點(diǎn)數(shù)據(jù)加索引，如果大量的查詢只對(duì)ID 由 100 ~ 200 的數(shù)據(jù)感興趣，就可以這樣做。

• 可以減小索引的大小
• 為據(jù)點(diǎn)數(shù)據(jù)提高查詢的性能。

總結(jié)：

BTree 索引有聚集與非聚集之分。
就查看上到聚集索引性能比非聚集索引性能要好。

非聚集索引分
覆蓋索引，唯一索引，復(fù)合索引（當(dāng)然聚集索引也有復(fù)合的，復(fù)合二字，只是說明索引，引用了多列），一般非聚集索引就查看上到非聚集索引中覆蓋索引的性能比別的非聚集索引性能要好，它的性能和聚集索引差不多，可是它也不是’銀彈‘ 它會(huì)用更多的磁盤空間。

最后說一下這個(gè)

with (drop_existing = on|off)，加上這個(gè)的意思是如果這個(gè)索引還在表上就drop 掉然后在create 一個(gè)新的。特別是在聚集索引上使用這個(gè)就可以不會(huì)引起非聚集索引的重建。

with (online = on|off） 創(chuàng)建索引時(shí)用戶也可以訪問表中的數(shù)據(jù)，

with(pad_index = on|off fillfactor = 80); fillfactor 用來設(shè)置填充百分比，pad_index 只是用來連接fillfactor 但是它又不能少，這點(diǎn)無語了。

with(allow_row_locks = on|off | allow_page_locks = on |off); 是否允許頁鎖 or 行鎖

with (data_compression = row | page ); 這樣可以壓縮索引大小

到此這篇關(guān)于SQLServer創(chuàng)建索引的5種方法小結(jié)的文章就介紹到這了,更多相關(guān)SQLServer創(chuàng)建索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論