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.

    Continues…

Leave a comment

Your email address will not be published.