2.4.2 聚集表上的非聚集索引
3. 管理索引
3.1 创建
CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED]
INDEX <index name> ON <table or view name>(<column name> [ASC|DESC] [,...n])
INCLUDE (<column name> [, ...n])
[WITH
[PAD_INDEX = { ON | OFF }]
[[,] FILLFACTOR = <fillfactor>]
[[,] IGNORE_DUP_KEY = { ON | OFF }]
[[,] DROP_EXISTING = { ON | OFF }]
[[,] STATISTICS_NORECOMPUTE = { ON | OFF }]
[[,] SORT_IN_TEMPDB = { ON | OFF }]
[[,] ONLINE = { ON | OFF }
[[,] ALLOW_ROW_LOCKS = { ON | OFF }
[[,] ALLOW_PAGE_LOCKS = { ON | OFF }
[[,] MAXDOP = <maximum degree of parallelism>
]
[ON {<filegroup> | <partition scheme name> | DEFAULT }]
INDEX <index name> ON <table or view name>(<column name> [ASC|DESC] [,...n])
INCLUDE (<column name> [, ...n])
[WITH
[PAD_INDEX = { ON | OFF }]
[[,] FILLFACTOR = <fillfactor>]
[[,] IGNORE_DUP_KEY = { ON | OFF }]
[[,] DROP_EXISTING = { ON | OFF }]
[[,] STATISTICS_NORECOMPUTE = { ON | OFF }]
[[,] SORT_IN_TEMPDB = { ON | OFF }]
[[,] ONLINE = { ON | OFF }
[[,] ALLOW_ROW_LOCKS = { ON | OFF }
[[,] ALLOW_PAGE_LOCKS = { ON | OFF }
[[,] MAXDOP = <maximum degree of parallelism>
]
[ON {<filegroup> | <partition scheme name> | DEFAULT }]
3.2 修改
ALTER INDEX { <name of index> | ALL }
ON <table or view name>
{ REBUILD
[ [ WITH (
[ PAD_INDEX = { ON | OFF } ]
| [[,] FILLFACTOR = <fillfactor>
| [[,] SORT_IN_TEMPDB = { ON | OFF } ]
| [[,] IGNORE_DUP_KEY = { ON | OFF } ]
| [[,] STATISTICS_NORECOMPUTE = { ON | OFF } ]
| [[,] ONLINE = { ON | OFF } ]
| [[,] ALLOW_ROW_LOCKS = { ON | OFF } ]
| [[,] ALLOW_PAGE_LOCKS = { ON | OFF } ]
| [[,] MAXDOP = <max degree of parallelism>
) ]
| [ PARTITION = <partition number>
[ WITH ( <partition rebuild index option>
[ ,...n ] ) ] ] ]
| DISABLE
| REORGANIZE
[ PARTITION = <partition number> ]
[ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
| SET ([ ALLOW_ROW_LOCKS= { ON | OFF } ]
| [[,] ALLOW_PAGE_LOCKS = { ON | OFF } ]
| [[,] IGNORE_DUP_KEY = { ON | OFF } ]
| [[,] STATISTICS_NORECOMPUTE = { ON | OFF } ]
)
} [ ; ]
ON <table or view name>
{ REBUILD
[ [ WITH (
[ PAD_INDEX = { ON | OFF } ]
| [[,] FILLFACTOR = <fillfactor>
| [[,] SORT_IN_TEMPDB = { ON | OFF } ]
| [[,] IGNORE_DUP_KEY = { ON | OFF } ]
| [[,] STATISTICS_NORECOMPUTE = { ON | OFF } ]
| [[,] ONLINE = { ON | OFF } ]
| [[,] ALLOW_ROW_LOCKS = { ON | OFF } ]
| [[,] ALLOW_PAGE_LOCKS = { ON | OFF } ]
| [[,] MAXDOP = <max degree of parallelism>
) ]
| [ PARTITION = <partition number>
[ WITH ( <partition rebuild index option>
[ ,...n ] ) ] ] ]
| DISABLE
| REORGANIZE
[ PARTITION = <partition number> ]
[ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
| SET ([ ALLOW_ROW_LOCKS= { ON | OFF } ]
| [[,] ALLOW_PAGE_LOCKS = { ON | OFF } ]
| [[,] IGNORE_DUP_KEY = { ON | OFF } ]
| [[,] STATISTICS_NORECOMPUTE = { ON | OFF } ]
)
} [ ; ]
3.3 删除
DROP INDEX <table name>.<index name>
4. 使用索引应注意十么
1)聚集索引通常速度优于非聚集索引
2) 建索引时应考虑是否有足够的空间。索引占据空间,平均约1.2倍数据库本身大小。
3) 在经常用于查询或聚合条件的字段上建立聚集索引。这类查询条件包括 between, >, <,group by, max,min, count等。
4) 不要在经常作为插入，且插入字段无序的列上建立聚集索引。 插入数据行会涉及分页，rebuild索引会消耗大量时间。参考文末"一个不恰当使用聚集索引的例子"。
5) 在值高度的唯一性字段上建立索引。不能在诸如性别的字段上建立索引。
6) 只有作为索引的第一个列包含在查询条件中，该索引才的作用。
打个比方，我们用偏旁+部首来查汉字，那么偏旁首先必须包括在查询条件中，只有先定位偏旁，再结合部首，才能发挥偏旁+部首来检索的快速功效。
7) 删除一直不用的索引。特别是对于删除和修改比较频繁的数据表，必须考虑如何精华索引。
