Online Indexing in SQL Server 2005

Transact SQL

Online indexing can be performed through the following T-SQL statements.

1. Create Index
   
   The syntax of Create Index is:
   
   CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
        ON <object> ( column [ ASC | DESC ] [ ,...n ] )
        [ INCLUDE ( column_name [ ,...n ] ) ]
        [ WITH ( <relational_index_option> [ ,...n ] ) ]
        [ ON { partition_scheme_name ( column_name )
             | filegroup_name
             | default
             }
        ]
   [ ; ]
   
   <object> ::=
   {
        [ database_name. [ schema_name ] . | schema_name. ]
             table_or_view_name
   }
   
   <relational_index_option> ::=
   {
        PAD_INDEX  = { ON | OFF }
        | FILLFACTOR = fillfactor
        | SORT_IN_TEMPDB = { ON | OFF }
        | IGNORE_DUP_KEY = { ON | OFF }
        | STATISTICS_NORECOMPUTE = { ON | OFF }
        | DROP_EXISTING = { ON | OFF }
        | ONLINE = { ON | OFF }
        | ALLOW_ROW_LOCKS = { ON | OFF }
        | ALLOW_PAGE_LOCKS = { ON | OFF }
        | MAXDOP = max_degree_of_parallelism
   }
   
   When we specify Online = ON, the clustered index is created online.
   
   Example:
   
   USE [AdventureWorks]
   GO
   CREATE CLUSTERED INDEX [PK_Employee_EmployeeID] ON [HumanResources].[Employee] ([EmployeeID] ASC)
   WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = ON) ON [PRIMARY]
   GO
2. Alter Index
   
   Syntax of Alter Index
   
   ALTER INDEX { index_name | ALL }
        ON <object>
             { REBUILD
                   [ [ WITH ( <rebuild_index_option> [ ,...n ] ) ]
                       | [ PARTITION = partition_number
                             [ WITH ( <single_partition_rebuild_index_option>
                                  [ ,...n ] )
                            ]
                       ]
                  ]
        | DISABLE
        | REORGANIZE
              [ PARTITION = partition_number ]
              [ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
        | SET ( <set_index_option> [ ,...n ] )
        }
   [ ; ]
   
   <object> ::=
   {
         [ database_name. [ schema_name ] . | schema_name. ]
             table_or_view_name
   }
   
   <rebuild_index_option > ::=
   {
        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
   }
   
   <single_partition_rebuild_index_option> ::=
   {
        SORT_IN_TEMPDB = { ON | OFF }
        | MAXDOP = max_degree_of_parallelism
   }
   
   <set_index_option>::=
   {
        ALLOW_ROW_LOCKS= { ON | OFF }
        | ALLOW_PAGE_LOCKS = { ON | OFF }
        | IGNORE_DUP_KEY = { ON | OFF }
   
   | STATISTICS_NORECOMPUTE = { ON | OFF }
   }
   
   The DBA can rebuild or reorganize the indexes of the underlying table depending on the fragmentation level. If the fragmentation level is between 5% and 30%, it is enough to reorganize the indexes. This operation is similar to DBCC INDEXDEFRAG statement. Reorganizing an index is always an online operation.
   
   
   << Prev Page     Next Page>>