Thursday, January 17, 2008


  ON table_name (column_name [ASC | DESC][,...n])
[FILLFACTOR = fillfactor]
[[,] [PAD_INDEX] = { ON | OFF }]
[[,] DROP_EXISTING  = { ON | OFF }]
[[,] IGNORE_DUP_KEY = { ON | OFF }]
[[,] SORT_IN_TEMPDB = { ON | OFF }]
[[,] ALLOW_ROW_LOCKS = { ON | OFF }]
[[,] MAXDOP = max_degree_of_parallelism]
[[,] ONLINE = { ON | OFF }]

SQL Server 2000 does not use ON and OFF. If you want an option set to ON, specify the option; if you don't want the option, don't specify it. SQL Server 2005 offers a backward-compatible syntax without the ON and OFF keywords, but for new development the new syntax is recommended.

To avoide index page spliting, assign the Fillfactor and PAD_Index.
Fillfactor generally applies only to the index's leaf page (the data page for a clustered index).
PAD_Index applies to the intermediate index pages, and use the same Fillfactor value.
If fillfactor is not specified, the server wilde default is used. The server wilde default value (is 0 by default) is set via the sp_configure procedure, with the fillfactor option. So if you want a fillfactor to apply to all databases on a server, set the server wilde default value.
Fillfactor and PAD_Index are only applicable when an index is created (or re-created). If there is a considerable number of transactions happending (such as bulk insert operation), you might need to rebuild the index as the bulk operation might filled up the reserved free space and caused page split which intern dropped the query performance.
When rebuild index, if no Fillfactor specified, the original Fillfactor value will be used.

Drop_Existing specifies that a given index should be dropped and rebuilt as a single transaction. It enhances performance when re-create a clustered index on a table with non-clustered indexes, as the non-clustered indexes will be ONLY re-created once after the clustered index is created and ONLY if the index keys changed (that is different index name or columns compare with the original index). Without this option, the non-clustered indexed will be re-created twice when a clustered index is re-created.
A clustered index can not be converted to non-clustered index using Drop_Existing. However, in SQL2000, a unique clustered index can be changed to non-unique index, and vice versa. In SQL 2005, all clustered indexes are unique. If you build a clustered index without specifying the UNIQUE keyword in SQL2005, SQL Server guarantees uniqueness internally by adding a uniqueifier to the rows when necessary.

Sort_in_TEMPDB: By default (without using this this option or turn it off) SQL Server uses space from the filegroup on which the index is to be created. When this option is used, sort buffers are allocated from tempdb, so much less space is needed in the source database. Also, when this option is used, performance can be grately improved if tempdb datbase is on a separate physical disk from the database you're working with. You can speed up index creation even more if tempdb database is on a faster disk than your user database and you use the SORT_IN_TEMPDB option. Alternative to using the SORT_IN_TEMPDB option, you can create separate filegroups for a table and its indexes, that is, the table is on one filegroup and its indexes are on another. If the two filegroups are on different disks, you can also minimize the disk head movement in return improve the index creation performance.

No comments: