Can I drop not null constraints online in SQL Server?

user_1862286 0
Viewed 342

I have a table with quite many rows (more than 300 000 000). I want to remove not null constraint for one of the columns by running the next SQL query while the database is still under load (since the table is big, it may take about 10 minutes):

ALTER TABLE DECLARATION
    ALTER COLUMN LOCAL_REFERENCE_NUMBER VARCHAR(22) NULL WITH (ONLINE = ON);

I expect this ONLINE = ON option to ensure that the table is not locked during the update to make sure that the applications that use the database can still do it during the update.

However, the docs say that ONLINE = ON is only applicable for adding and removing indexes as well as primary key or unique constraints, i.e., as it seems from the official documentation, this option has no effect for the not null constraints.

Is it indeed the case, or the documentation is just not full? If that is the case, what is so special about dropping not null constraints that it cannot be done online?

Thank you.

sql-server database
1 Answers

As stated in commment section this operation should be metadata operation only(if no data type changes occured):

ALTER TABLE DECLARATION
    ALTER COLUMN LOCAL_REFERENCE_NUMBER VARCHAR(22) NULL;

It could be verified by setting Extended Event session and observing sqlserver.compressed_alter_column_is_md_only event (SQL Server 2016+)

user_50708790
Related