Perform index operations online

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

This article describes how to create, rebuild, or drop indexes online 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 (X) locks on the underlying data and associated indexes. This prevents modifications and queries to the underlying data until the index operation is complete.

Note

Index rebuild commands might hold exclusive locks on clustered indexes after a large object column is dropped from a table, even when performed online.

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

• CREATE INDEX
• ALTER INDEX
• DROP INDEX
• ALTER TABLE (To add or drop UNIQUE or PRIMARY KEY constraints)

For limitations and restrictions concerning creating, rebuilding, or dropping indexes online, see Guidelines for online index operations.

To use resumable index operations, an index operation must be performed online. For more information, see Resumable index considerations.

Supported platforms

Online index operations aren't available in every edition of SQL Server. For more information, see Editions and supported features of SQL Server 2022.

Online index operations are available in Azure SQL Database and Azure SQL Managed Instance.

Permissions

Requires the ALTER permission on the table or view.

Use SQL Server Management Studio

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

2. Expand the Tables folder.

3. Expand the table on which you want to rebuild an index online.

4. Expand the Indexes folder.

5. Use the context menu for 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. Select OK.

9. Use the context menu for 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 select OK.

Use Transact-SQL

The following example rebuilds an existing online index in the AdventureWorks sample database.

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

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.

-- 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 AdventureWorks2022
        ADD FILEGROUP NewGroup;
    ALTER DATABASE AdventureWorks2022
        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');

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

Related content

• Guidelines for online index operations
• How online index operations work