Performing Index Operations Online
In SQL Server 2005, you can create, rebuild, or drop indexes online. 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 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 available only in SQL Server 2005 Enterprise Edition.
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.
- CREATE INDEX
- ALTER INDEX
- DROP INDEX
- ALTER TABLE (To add or drop UNIQUE or PRIMARY KEY constraints with CLUSTERED index option)
For more information about online index operations, see How Online Index Operations Work and Guidelines for Performing Online Index Operations.
Example
In the following example, all indexes on the Product table in the AdventureWorks sample database are rebuilt online.
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (ONLINE = ON);
See Also
Concepts
Disabling Indexes
Reorganizing and Rebuilding Indexes
Locking in the Database Engine
Understanding Row Versioning-Based Isolation Levels
Other Resources
ALTER INDEX (Transact-SQL)
ALTER TABLE (Transact-SQL)
CREATE INDEX (Transact-SQL)