Does Alter Table Add Column with Default Value work the same on Standard and on Enterprise

Question

Does Alter Table Add Column with Default Value work the same on Standard and on Enterprise

Miles P 20

SQL Server 2019 Standard Edition

ALTER TABLE CommunicationsRecipient ADD CausedUnsubscribe Bit NOT NULL DEFAULT 0;

I had a 3rd party software upgrade that failed on an Alter Table Add column statement on a 45 million row table. The Upgrade has a built-in 6 minute timeout so any tSQL that runs for 6 minutes will be aborted. My workaround was to rename the table and Alter it(25 minutes), create a new replacement table, rerun the upgrade against this new empty table, Delete the new empty table, and then change the name of the renamed-Altered table back to the original name.

I think that the reason that the Alter Table Add Column with Default Value takes 25 minutes is because we use Standard Edition and that it would be almost instantaneous on Enterprise Edition. But I need to be sure before presenting the option of going from the Standard Edition to Enterprise which costs quite a bit more. Does the above tsql statement operate differently on 2019 Standard and 2019 Enterprise? Does any of the newer Standard Editions run this tSQL statement the same way as on the Enterprise Edition? .... Using sys.system_internals_partition_columns.is_nullable/default_value?

Accepted answer

1 additional answer

Your answer

Answer 1

Erland Sommarskog 124.2K MVP Volunteer Moderator

Your observation is correct. Adding a non-nullable column with a default value is a size-of-data of operation in Standard Edition, because every row needs to be updated.

In Enterprise Edition there is an optimisation that makes this operation a metadata- only operation. The default value is not written until the row is touched. (Which could be an index rebuild.)

Miles P 20 Reputation points

2025-07-23T18:05:50.16+00:00

Erland,

You confirmed my suspicion re Enterprise vs Standard editions.

It will be hard to convince management to upgrade to the Enterprise Edition, not because of day-to-day business needs but because of how the Upgrade Software is designed.

One solution is for the vendor to remove the 6-minute abort restriction so that the upgrade will run through.

After I write this comment, I will research the newest Standard Edition just in case this metadata-only feature has been included in it.

Thanks.
Erland Sommarskog 124.2K Reputation points MVP Volunteer Moderator

2025-07-23T18:49:27.3766667+00:00

This works the same in SQL 2022, and I would not expect any changes in SQL 2025 either.

I agree that upgrading to Enterprise Edition only because of this is a tough sell. You need to work with the vendor. I suspect that they only tested on Developer Edition which has the same feature set as Enterprise. Or they simply don't have a test database of your size.

Removing the sex-minute timeout - which seems quite useless anyway - is a start. But I think they should consider to implement this as create a new table and copy over data in batches. At least for future update packages.
Ben Miller-(DBADuck) 195 Reputation points MVP

2025-07-29T14:36:02.25+00:00

You can always add the column with a NULL and then update the rows as you go to get the default value in the rows, then add a default constraint to the column and then alter the column to be NOT NULL. It is a round about way to go, but you are in Standard so you have to think outside the box to accomplish the same thing. Just an idea.

Answer 2

Bruce (SqlWork.com) 79,101 Volunteer Moderator

While sql enterprise has scaling features not in standard, it is unlikely the alter table would be that much faster. As the alter may require altering every row, the performance is mostly based on the table size and disk performance.

Miles P 20 Reputation points

2025-07-23T15:44:36.68+00:00

Bruce, thanks for the response but I think that there is more to this than table-size and disk performance. There are DBAs that run a similar Alter statement on even larger tables in under a second. What happens is that the table data is not updated but instead the metadata is change in the is_nullable and default_value columns of the sys.system_internals_partition_columns table. This is not happening in my case so the only thing that I can think is that Standard Edition does not use the metadata method but instead it update the table data. Thanks again.
Miles P 20 Reputation points

2025-07-23T15:45:12.2233333+00:00
Bruce....

select p.index_id, p.partition_number

, pc.leaf_null_bit , coalesce(cx.name, c.name) as column_name , pc.partition_column_id , pc.max_inrow_length , pc.max_length , pc.key_ordinal , pc.leaf_offset , pc.is_nullable , pc.default_value

from sys.system_internals_partitions p

join sys.system_internals_partition_columns pc on p.partition_id = pc.partition_id

left join sys.index_columns ic on p.object_id = ic.object_id and ic.index_id = p.index_id and ic.index_column_id = pc.partition_column_id

left join sys.columns c on p.object_id = c.object_id and ic.column_id = c.column_id

left join sys.columns cx on p.object_id = cx.object_id and p.index_id in (0, 1) and pc.partition_column_id = cx.column_id

where p.object_id = object_id('[CommunicationRecipient]')

And coalesce(cx.name, c.name)= 'CausedUnsubscribe'

order by index_id, partition_number

Share via

Does Alter Table Add Column with Default Value work the same on Standard and on Enterprise

1 additional answer

Your answer