[sql] What is the difference between OFFLINE and ONLINE index rebuild in SQL Server?

When rebuilding an index, there is an option for ONLINE=OFF and ONLINE=ON. I know that when ONLINE mode is on, it makes a copy of the index, switches new queries to utilizing it, and then rebuilds the original index, tracking changes using versioning to both (correct me if I am wrong).

But what does SQL do in OFFLINE mode?

This question is related to sql indexing

The answer is


The main differences are:

1) OFFLINE index rebuild is faster than ONLINE rebuild.

2) Extra disk space required during SQL Server online index rebuilds.

3) SQL Server locks acquired with SQL Server online index rebuilds.

  • This schema modification lock blocks all other concurrent access to the table, but it is only held for a very short period of time while the old index is dropped and the statistics updated.

Online index rebuilds are less intrusive when it comes to locking tables. Offline rebuilds cause heavy locking of tables which can cause significant blocking issues for things that are trying to access the database while the rebuild takes place.

"Table locks are applied for the duration of the index operation [during an offline rebuild]. An offline index operation that creates, rebuilds, or drops a clustered, spatial, or XML index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements."

http://msdn.microsoft.com/en-us/library/ms188388(v=sql.110).aspx

Additionally online index rebuilds are a enterprise (or developer) version only feature.