I recently added a NULL-able bit column to a table which has close to 500 million rows. There's not a 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 cancelled would take to complete?
I am using SQL Server 2017 Web Edition.