SQL 2005- Online Indexing

In SQL Server 2005 Enterprise Edition we can create an index with option ONLINE, this option states whether the underlying tables and indexes will be available for queries and data modification while indexing operations are taking place. we can define its settings to OFF or ON:

while OFF Table locks are applied for the duration of the index operation. Clustered index operations acquires a schema lock, which prevents all user access to the underlying table for the duration of the index operation. Nonclustered operations acquire a shared lock on the table that allows for read operations but prevents data modification.

While ON Table locks are not held for the duration of the index operation. SQL Server will first acquire an intent share lock, which allows queries and data modifications. Then SQL Server acquires a shared lock at the start of the index operation and quickly releases it. If a nonclustered index is being created, SQL Server will acquire a shared lock again at the end of the operation. If a clustered index is being created or dropped, or a nonclustered index is being rebuilt, SQL Server acquires a schema modification lock at the end of the operation. ONLINE can’t be set to ON for indexes created on local temporary tables (tables whose names start with the # character).