perform-index-operations-online.md

title	description	author	ms.author	ms.reviewer	ms.date	ms.service	ms.subservice	ms.topic	helpviewer_keywords	monikerRange
Perform index operations online	Create, rebuild, or drop indexes online in the SQL Server Database Engine.	MikeRayMSFT	mikeray	randolphwest	03/04/2024	sql	table-view-index	conceptual	index online operations [SQL Server] online index operations ONLINE option	=azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current

Perform index operations online

[!INCLUDE SQL Server Azure SQL Database Azure SQL MI]

This article describes how to create, rebuild, or drop indexes online in [!INCLUDE ssnoversion] by using [!INCLUDE ssManStudioFull] or [!INCLUDE tsql]. 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.

Supported platforms

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

Online index operations are available in [!INCLUDE ssazure-sqldb] and [!INCLUDE ssazuremi-md].

Limitations

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 [!INCLUDE tsql] statements.

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

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

Permissions

Requires ALTER permission on the table or view.

Use SQL Server Management Studio

1. In Object Explorer, select 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. Select 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. Select 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 select OK.

Use Transact-SQL

The following example rebuilds an existing online index in the AdventureWorks 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.

:::code language="sql" source="codesnippet/tsql/perform-index-operations_1.sql":::

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

Related content

• Resumable index considerations