SQL Server Create Index语法

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
ON table_name (column_name [ASC | DESC][,...n])
[WITH
[FILLFACTOR = fillfactor]
[[,] [PAD_INDEX] = { ON | OFF }]
[[,] DROP_EXISTING = { ON | OFF }]
[[,] IGNORE_DUP_KEY = { ON | OFF }]
[[,] SORT_IN_TEMPDB = { ON | OFF }]
[[,] STATISTICS_NORECOMPUTE = { ON | OFF }]
[[,] ALLOW_ROW_LOCKS = { ON | OFF }]
[[,] ALLOW_PAGE_LOCKS = { ON | OFF }]
[[,] MAXDOP = max_degree_of_parallelism]
[[,] ONLINE = { ON | OFF }]
]

(1) FILLFACTOR :

在创建索引时为每个leaf页面保留多少自由空间

sp_configure fillfactor

(2) PAD_INDEX
让FILLFACTOR对intermediate index pages也有效

(3) DROP_EXISTING
用于索引重建. 对于cluster index特别有用,可以防止该表上的non cluster index被重建两次

(4) IGNORE_DUP_KEY
用于unique index, 对于一个多行insert语句,如果有部分行与现有数据重复,那么这些重复的行将不被插入,其余行可以被插入.

(5) SORT_IN_TEMPDB
让排序在tempdb里而不是数据表所在的filegroup里发生

(6) STATISTICS_NORECOMPUTE
是否自动更新索引的统计数据

如果AUTO_UPDATE_STATISTICS=off, 那么 STATISTICS_NORECOMPUTE=off无效
如果AUTO_UPDATE_STATISTICS=on, 那么 STATISTICS_NORECOMPUTE = on 有效

(7) ALLOW_ROW_LOCKS

(8) ALLOW_PAGE_LOCKS

(9) MAXDOP

(10) ONLINE

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/100682/viewspace-1021767/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/100682/viewspace-1021767/