0

Alter table column from NULL to NOT NULL

Posted on Monday, October 20, 2014 by 醉·醉·鱼 and labeled under
从SQL SERVER 2012开始，Alter table column from NULL to NOT NULL with default value is metadata change.

http://msdn.microsoft.com/en-us/library/ms190273.aspx

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. A runtime constant is an expression that produces the same value at runtime for each row in the table regardless of its determinism. For example, the constant expression "My temporary data", or the system function GETUTCDATETIME() are runtime constants. In contrast, the functions NEWID() or NEWSEQUENTIALID() are not runtime constants because a unique value is produced for each row in the table. Adding a NOT NULL column with a default value that is not a runtime constant is always performed offline and an exclusive (SCH-M) lock is acquired for the duration of the operation.
除了文中说的例外，data compression也会有影响的。所以，还是得小心啊。
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6784c3d8-2e40-47fd-956a-910c576a2266/changing-column-in-not-null-why-is-there-so-much-transaction-log-activity-on-compressed-tables?forum=sqldatabaseengine

此外，它依旧还是会有3个操作，所以http://dba.stackexchange.com/a/29526/37769 里面描述的依旧有效。

  1. 创建一个新的column
  2. 为新的column赋值
  3. 将前一个column标记dropped
0
Responses to ... Alter table column from NULL to NOT NULL

Post a Comment