Blocking while creating index?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Hassan - 29 Dec 2007 01:11 GMT

If I dont use online indexing, will there be blocking for the entire
duration of the index creation be it clustered or non clustered ? I thought
there might be an exclusive lock on the table for the entire duration where
even selects would be blocked...but it doesnt appear to be true.

Please let me know

Using SQL 2005

Reply to this Message

Ben Nevarez - 29 Dec 2007 01:29 GMT

See BOL, Alter Index, Online OFF

"Table locks are applied for the duration of the index operation. An offline
index operation that creates, rebuilds, or drops a clustered 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."

Hope this helps,

Ben Nevarez
Senior Database Administrator
AIG SunAmerica

> If I dont use online indexing, will there be blocking for the entire
> duration of the index creation be it clustered or non clustered ? I thought
[quoted text clipped - 4 lines]
>
> Using SQL 2005

Reply to this Message