Perform Index Operations Online

This topic describes how to create, rebuild, or drop indexes online in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL. The ONLINE option allows concurrent user access to the underlying table or clustered index data and any associated nonclustered indexes during these index operations. For example, while a clustered index is being rebuilt by one user, that user and others can continue to update and query the underlying data. When you perform data definition language (DDL) operations offline, such as building or rebuilding a clustered index; these operations hold exclusive locks on the underlying data and associated indexes. This prevents modifications and queries to the underlying data until the index operation is complete.

Note

Online index operations are not available in every SQL Server edition. For more information, see Features Supported by the Editions of SQL Server 2012.

In This Topic

  • Before you begin:

    Limitations and Restrictions

    Security

  • To rebuild an index online, using:

    SQL Server Management Studio

    Transact-SQL

Before You Begin

Limitations and Restrictions

  • We recommend performing online index operations for business environments that operate 24 hours a day, seven days a week, in which the need for concurrent user activity during index operations is vital.

  • The ONLINE option is available in the following Transact-SQL statements.

  • For more limitations and restrictions concerning creating, rebuilding, or dropping indexes online, see Guidelines for Online Index Operations.

Security

Permissions

Requires ALTER permission on the table or view.

Arrow icon used with Back to Top link [Top]

Using SQL Server Management Studio

To rebuild an index online

  1. In Object Explorer, click the plus sign to expand the database that contains the table on which you want to rebuild an index online.

  2. Expand the Tables folder.

  3. Click the plus sign to expand the table on which you want to rebuild an index online.

  4. Expand the Indexes folder.

  5. Right-click the index that you want to rebuild online and select Properties.

  6. Under Select a page, select Options.

  7. Select Allow online DML processing, and then select True from the list.

  8. Click OK.

  9. Right-click the index that you want to rebuild online and select Rebuild.

  10. In the Rebuild Indexes dialog box, verify that the correct index is in the Indexes to rebuild grid and click OK.

Arrow icon used with Back to Top link [Top]

Using Transact-SQL

To create, rebuild, or drop an index online

  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute. The example rebuilds an existing online

    USE AdventureWorks2012;
    GO
    ALTER INDEX AK_Employee_NationalIDNumber ON HumanResources.Employee
    REBUILD WITH (ONLINE = ON);
    GO
    

    The following example deletes a clustered index online and moves the resulting table (heap) to the filegroup NewGroup by using the MOVE TO clause. The sys.indexes, sys.tables, and sys.filegroups catalog views are queried to verify the index and table placement in the filegroups before and after the move.

    USE AdventureWorks2012;
    GO
    --Create a clustered index on the PRIMARY filegroup if the index does not exist.
    IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 
                N'AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate')
        CREATE UNIQUE CLUSTERED INDEX
            AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate 
        ON Production.BillOfMaterials (ProductAssemblyID, ComponentID, 
            StartDate)
        ON 'PRIMARY';
    GO
    -- Verify filegroup location of the clustered index.
    SELECT t.name AS [Table Name], i.name AS [Index Name], i.type_desc,
        i.data_space_id, f.name AS [Filegroup Name]
    FROM sys.indexes AS i
        JOIN sys.filegroups AS f ON i.data_space_id = f.data_space_id
        JOIN sys.tables as t ON i.object_id = t.object_id
            AND i.object_id = OBJECT_ID(N'Production.BillOfMaterials','U')
    GO
    --Create filegroup NewGroup if it does not exist.
    IF NOT EXISTS (SELECT name FROM sys.filegroups
                    WHERE name = N'NewGroup')
        BEGIN
        ALTER DATABASE AdventureWorks2012
            ADD FILEGROUP NewGroup;
        ALTER DATABASE AdventureWorks2012
            ADD FILE (NAME = File1,
                FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\File1.ndf')
            TO FILEGROUP NewGroup;
        END
    GO
    --Verify new filegroup
    SELECT * from sys.filegroups;
    GO
    -- Drop the clustered index and move the BillOfMaterials table to
    -- the Newgroup filegroup.
    -- Set ONLINE = OFF to execute this example on editions other than Enterprise Edition.
    DROP INDEX AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate 
        ON Production.BillOfMaterials 
        WITH (ONLINE = ON, MOVE TO NewGroup);
    GO
    -- Verify filegroup location of the moved table.
    SELECT t.name AS [Table Name], i.name AS [Index Name], i.type_desc,
        i.data_space_id, f.name AS [Filegroup Name]
    FROM sys.indexes AS i
        JOIN sys.filegroups AS f ON i.data_space_id = f.data_space_id
        JOIN sys.tables as t ON i.object_id = t.object_id
            AND i.object_id = OBJECT_ID(N'Production.BillOfMaterials','U');
    GO
    

For more information, see ALTER INDEX (Transact-SQL).

Arrow icon used with Back to Top link [Top]