Adding not-null column to large table SQL Server 2012 behaves differently on different servers

Question

In SQL Server 2008/2012, adding a null column to a large table (with default null) is more or less instant.

ALTER TABLE TableWithManyRows
    ADD TestNull INT NULL;

In SQL Server 2012, this should also be the case for not-null columns being set to a default value - see http://rusanu.com/2011/07/13/online-non-null-with-values-column-add-in-sql-server-11/

The following statement:

ALTER TABLE TableWithManyRows
    ADD TestWithValues INT NOT NULL DEFAULT 0 WITH VALUES;

run more or less instantly on my local machine, but very slow on the production database server.

Both are running SQL Server 2012. Running the "Null" version is instant on both, it's just the non-null case where I am seeing differences.

Is there a configuration option that could influence this? Any other suggestions on how I can track down the difference?

Solution (in case someone doesn't spot it in the comments below)

I was confused because locally I have the developer edition, which behaves like the Enterprise edition. The production server didn't have the enterprise edition.

Answer 1

ALTER TABLE TableWithManyRows
    ADD TestNull INT NULL;

Does nothing to the table's records

ALTER TABLE TableWithManyRows
    ADD TestWithValues INT NOT NULL DEFAULT 0 WITH VALUES;

Updates every record for that column to a value of 0. With a large amount of data this will take time.

Not a lot you can do about that if that is your business rule.