Improved responsiveness and availability in busy production environments, where making a table unavailable for minutes or hours is not practical.
The ability to adjust the balance between performance and concurrency during a DDL operations using the
LOCK=EXCLUSIVEblocks access to the table entirely.
LOCK=SHAREDallows queries but not DML.
LOCK=NONEallows full query and DML access to the table.
LOCK=DEFAULTor omitting the
LOCKclause permits as much concurrency as possible depending on the type of DDL operation.
Avoidance of disk space usage and I/O overhead associated with copying the table and reconstructing secondary indexes.