1

SQL Server 2014 Standard Edition

In my situation, I have a huge table (100 million+ rows), and need to:

  • add column to it
  • set default value in that column

I see in https://technet.microsoft.com/en-us/library/ms190273(v=sql.110).aspx that there is a new lock_escalation option.

Does this allow me to, for example, do the following without a table lock:

e.g.

ALTER TABLE Protocols set (LOCK_ESCALATION = DISABLE);
go

ALTER TABLE Protocols
ADD ProtocolTypeID int NOT NULL DEFAULT(1)
GO

However, the concerns are:

a) LOCK_ESCALATION = DISABLE -- what exactly is it for, does it apply here? (The SQL Server docs are a bit thin... perhaps it only applies to partitioned tables?)

b) Does LOCK_ESCALATION = DISABLE affect the transaction size? (Appears it will still create one huge transaction?)

c) Will LOCK_ESCALATION = DISABLE even apply and avoid a table lock?

d) Is there a way to chunk it? (Can't imagine, but there is so much new magic in newer SQL versions... I should ask!)

  • 1
    Why would you want to avoid a tablelock in this scenario? If you are altering the sctructure of the table and adding a new value then all pages that the table contains are going to have to be altered to have this column/value added. This will probably result in lots data being shuffled around to fit this new column. It seems like having the table locked during such a transaction would be advantageous. – Zane Sep 16 '15 at 16:23
  • 1
    possible duplicate of Adding columns to production tables – Andriy M Sep 16 '15 at 16:23
  • @Zane This question is an exploration of this new flag for alter_table. Your concern is valid. – Jonesome Reinstate Monica Sep 16 '15 at 16:32
5

You say the documentation is thin, but the answers are all there.

Lock Escalation

You really need to read that whole page, but essentially lock escalation only applies to 'regular' locks (e.g. shared, exclusive). Operations that change the structure of an object take much more restrictive schema locks (e.g. Sch-M) which prevent all concurrent access to the object being modified. As such, less restrictive 'ordinary' locks are not required. Ultimately, the question you ask is moot, because ALTER TABLE does not take row or page level locks that might be escalated to a partition or object level lock.

Regarding ALTER_TABLE (in the Remarks section):

Locks and ALTER TABLE

The changes specified in ALTER TABLE are implemented immediately. If the changes require modifications of the rows in the table, ALTER TABLE updates the rows. ALTER TABLE acquires a schema modify (SCH-M) lock on the table to make sure that no other connections reference even the metadata for the table during the change, except online index operations that require a very short SCH-M lock at the end. In an ALTER TABLE…SWITCH operation, the lock is acquired on both the source and target tables. The modifications made to the table are logged and fully recoverable. Changes that affect all the rows in very large tables, such as dropping a column or, on some editions of SQL Server, adding a NOT NULL column with a default value, can take a long time to complete and generate many log records. These ALTER TABLE statements should be executed with the same care as any INSERT, UPDATE, or DELETE statement that affects many rows.

and:

Adding NOT NULL Columns as an Online Operation

Starting with SQL Server 2012 Enterprise Edition, adding a NOT NULL column with a default value is an online operation when the default value is a runtime constant. This means that the operation is completed almost instantaneously regardless of the number of rows in the table. This is because the existing rows in the table are not updated during the operation; instead, the default value is stored only in the metadata of the table and the value is looked up as needed in queries that access these rows. This behavior is automatic; no additional syntax is required to implement the online operation beyond the ADD COLUMN syntax.

If you were using Enterprise Edition, the change you propose could be an instant, metadata only change (though any subsequent changes to data in the new column would require real allocations, data movement, and logging, of course).

As it is, adding this column will be an expensive, long-running, fully-logged, single transaction, size-of-data operation. There is no way to 'chunk' this change without changing the definition of the column (a nullable column can be added as a metadata-only change, then updated in chunks).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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