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…

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |