SQL Server 2012 takes a long time to just change to add NULL columns

Question

I have a problem modifying a table in SQL Server 2012, to add the 04 columns allowed NULL, I need a large table with 340 columns and approximately 166 M rows and 01 non-clustered index

When recovering from this problem, only a specific table occurs. I am waiting for execution within 10 hours, but it is not finished, so I must cancel it for further investigation. This is so weird because the script is really simple, as shown below, and we have done it successfully before:

alter table sample_database.sample_schema.sample_table
add column_001 int null
   ,column_002 numeric(18,4) null
   ,column_003 nvarchar(500) null
   ,column_004 int null;

My questions:

• Why is this happening weird?
• How to solve this because it is related to our deployment package? We made a workaround like creating a new table with new columns and loading data. But that will not work for us.
• How to prevent this problem in the future?

Many thanks,

Answer 1

This incident occurred in our UAT environment. After we restore the database from the last backup again, this problem will not happen again. Change completed within seconds.

In my opinion, there has been a problem since the last recovery.

Many thanks,

Answer 2

If this is 2012 (according to the tag), this could happen:

http://rusanu.com/2012/02/16/adding-a-nullable-column-can-update-the-entire-table/

SQL Server 2012 8060, ALTER , . SQL Server 2012.

Answer 3

@Anton. -Page Split

:

• (, SELECT * INTO <New table> from sample_table WHERE 0=1)
• .
• sample_table .
• 3 sample_table sample_table.

NOT NULL , .