CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX index_name ON table_name (column_name…) [WITH FILLFACTOR=x] UNIQUE表示唯一索引,可選 CLUSTERED、NONCLUSTERED表示聚集索引還是非聚集索引,可選 FILLFACTOR表示填充因子,指定一個0到100之間的值,該值指示索引頁填滿的空間所佔的百分比
在stuMarks表的writtenExam列建立索引:
USE stuDBGO
IF EXISTS (SELECT name FROM sysindexes WHERE name = "IX_writtenExam") DROP INDEX stuMarks.IX_writtenExam /*--筆試列建立非聚集索引:填充因子為30%--*/CREATE NONCLUSTERED INDEX IX_writtenExam ON stuMarks(writtenExam) WITH FILLFACTOR= 30GO/*-----指定按索引 IX_writtenExam 查詢----*/SELECT * FROM stuMarks (INDEX=IX_writtenExam) WHERE writtenExam BETWEEN 60 AND 90
如何建立索引 :
使用T-SQL語句建立索引的語法:
CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX index_name ON table_name (column_name…) [WITH FILLFACTOR=x] UNIQUE表示唯一索引,可選 CLUSTERED、NONCLUSTERED表示聚集索引還是非聚集索引,可選 FILLFACTOR表示填充因子,指定一個0到100之間的值,該值指示索引頁填滿的空間所佔的百分比
在stuMarks表的writtenExam列建立索引:
USE stuDBGO
IF EXISTS (SELECT name FROM sysindexes WHERE name = "IX_writtenExam") DROP INDEX stuMarks.IX_writtenExam /*--筆試列建立非聚集索引:填充因子為30%--*/CREATE NONCLUSTERED INDEX IX_writtenExam ON stuMarks(writtenExam) WITH FILLFACTOR= 30GO/*-----指定按索引 IX_writtenExam 查詢----*/SELECT * FROM stuMarks (INDEX=IX_writtenExam) WHERE writtenExam BETWEEN 60 AND 90
雖然我們可以指定SQL Server按哪個索引進行資料查詢,但一般不需要我們人工指定。SQL Server將會根據我們建立的索引,自動最佳化查詢。