How to Increase speed altering column on a large table to NON-NULL?
I recently added a NULL-able bit column to a table that has close to 500 million rows. There's no default on the column, however, all inserts are specifying a value of 0 or 1, and I ran a one-time routine to assign 0 or 1 to all existing rows (updating the rows in small batches). Every row should now have a 0 or 1 in that column. I want to make the bit column non-nullable however when I tried doing so via ALTER TABLE t1 ALTER COLUMN c1 bit not null, it started running for 3 minutes and I stopped it because it was blocking all reads to the table and I suspected it was going to take a long time to complete. It's possible it wouldn't take too long but I couldn't risk too much unavailability. The rollback itself took 6 minutes.
Do you have any suggestions on how I can make the column non-nullable without it taking potentially hours to complete? Additionally is there any way to estimate how long the ALTER TABLE ALTER COLUMN statement I started and then canceled would take to complete? I am using SQL Server 2017 Web Edition.
Instead of changing the column definition you could add a CHECK CONSTRAINT that doesn't allow NULLs for that column. The table will still need to be scanned but it won't need to modify every single data page, so it should be a much faster operation. Regrettably, an Sch-M lock will still be held during the operation. One trick is to try to get as much of the table into the buffer pool as possible before attempting to add the constraint. That may reduce the amount of time the Sch-M lock is held. You can then remove the constraint and change the column definition during your next maintenance window and solve SQL server alter column not null.