5

I want to run the following on a table of about 12 million records.

ALTER TABLE t1
ADD c1 int NULL;
ALTER TABLE t2
ADD c2 bit NOT NULL
DEFAULT(0);

I've done it in staging and the timing seemed fine, but before I do it in production, I wanted to know how locking works on the table during new column creation (especially when a default value is specified). So, does anyone know? Does the whole table get locked, or do the rows get locked one by one during default value insertion? Or does something different altogether happen?

Eli
  • 36,793
  • 40
  • 144
  • 207

4 Answers4

14

Prior to SQL Server 11 (Denali) the add non-null column with default will run an update behind the scenes to populate the new default values. Thus it will lock the table for the duration of the 12 million rows update. In SQL Server 11 this is no longer the case, the column is added online and no update occurs, see Online non-NULL with values column add in SQL Server 11.

Both in SQL Server 11 and prior a Sch-M lock is acquired on the table to modify the definition (add the new column metadata). This lock is incompatible with any other possible access (including dirty reads). The difference is in the duration: prior to SQL Server 11 this lock will be hold for a size-of-data operation (update of 12M rows). In SQL Server 11 the lock is only held for a short brief. In the pre-SQL Server 11 update of the rows no row lock needs to be acquired because the Sch-M lock on the table guarantees that there cannot be any conflict on any individual row.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
6

Yes, it will lock the table.

A table, as a whole, has a single schema (set of columns, with associated types). So, at a minimum, a schema lock would be required to update the definition of the table.

Try to think about how things would work contrariwise - if each row was updated individually, how would any parallel queries work (especially if they involved the new columns)?

And default values are only useful during INSERT and DDL statements - so if you specify a new default for 10,000,000 rows, that default value has to be applied to all of those rows.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • "default values are only useful during INSERT and DDL statements" -- what about the referential actions ON **UPDATE** SET DEFAULT and ON **DELETE** SET DEFAULT ? ;) – onedaywhen Jul 20 '11 at 20:12
  • I understand that it would lock during updating the *definition* of the table, but would that lock stay while it's updating with the default value for each row? Updating the table definition is really quick, maybe 2 seconds. Updating each row with the default value is no where near so quick, so this is where performance issues would arise, if anywhere. – Eli Jul 20 '11 at 20:20
  • If the column is going to be not null, you need a default value just to add it. – HLGEM Jul 20 '11 at 22:14
6

Yes, it will lock.

DDL statements issue a Schema Lock (see this link) which will prevent access to the table until the operation completes.

There's not really a way around this, and it makes sense if you think about it. SQL needs to know how many fields are in a table, and during this operation some rows will have more fields than others.

The alternative is to make a new table with the correct fields, insert into, then rename the tables to swap them out.

JNK
  • 63,321
  • 15
  • 122
  • 138
0

I have not read how the lock mechanism works when adding a column, but I am almost 100% sure row by row is impossible.

Watch when you do these types of things in SQL Server Manager with drag and drop (I know you are not doing this here, but this is a public forum), as some changes are destructive (fortunately, SQL Server 2008, at least R2, is safer here as it tells you "no can do" rather than just do it).

You can run both column additions in a single statement, however, and reduce the churn.

Gregory A Beamer
  • 16,870
  • 3
  • 25
  • 32