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@]
