1

We have SQL Server 2019 Standard Edition

Microsoft SQL Server 2019 (RTM-CU10) (KB5001090) - 15.0.4123.1 (X64) Mar 22 2021 18:10:24 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

where index creation is completely offline operation. So every CREATE INDEX statement render the table unavailable for reading/writing. I partitioned one table and to my big surprise when I was creating all the nonclustered indexes on the same partition scheme this table was perfectly readable. Is it a documented feature and where can I read about it? All data modifications of this table was still blocked but there was no blocked readers at all. This database has RCSI enabled if it could matter.

1 Answer 1

3

Is it a documented feature and where can I read about it?

Yes. Below is the excerpt from the ONLINE = { ON | OFF } section of the documentation regarding OFF behavior. I added emphasis in bold.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.