By Ben Amada

2019-06-07 12:51:15 8 Comments

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.


@Razvan Socol 2019-06-09 16:13:25

Try copying the data to a new table, then rename it. You have to take care of any constraints and indexes. That's what SSMS table designer is doing when you want to reorder the columns (for example), but you should check the script to see if there is anything that doesn't look right.

During the copying, read access to the source table is not a problem, but if there are any writes they may be blocked or not copied, depending on the isolation level.

@Joe Obbish 2019-06-07 15:38:16

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, a 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.

@Ben Amada 2019-06-10 19:07:22

Thanks Joe for the idea. I almost went with adding a check constraint but had a maintenance window over the weekend and was able to alter the column to be non-nullable. I'm not sure if it helped, but to try and get the table data into the buffer pool, right before making the column non-nullable, I ran SELECT c1, count(*) FROM t1 GROUP BY c1 which took about 9 minutes to run. The actual ALTER TABLE ALTER COLUMN statement after that took 25 minutes to complete. Not too bad.

@Comment Converter 2019-06-07 14:32:20

If you are on Enterprise Edition (EE) a better strategy might have been to add it as NOT NULL with a default of 0 or 1 (whichever is most common).

This is a metadata-only change in EE. Then update the ones needing to be flipped. This means fewer updates and no need to change the column nullability when finished. - martin-smith

@Ben Amada 2019-06-10 19:01:55

Interestingly I noticed this feature last week when I tested adding a non-nullable bit column with a default value to this same large table on my local machine which is running SQL developer edition -- the column added instantly and I couldn't figure out why. It later him me that it must be because developer edition includes EE features.

Related Questions

Sponsored Content

1 Answered Questions

[SOLVED] Is ALTER TABLE ... DROP COLUMN really a metadata only operation?

2 Answered Questions

Alter All Data Types To Different Data Type

5 Answered Questions

2 Answered Questions

[SOLVED] Quickly change NULL column to NOT NULL

2 Answered Questions

[SOLVED] Does ALTER TABLE - ADD COLUMN lock the table?

2 Answered Questions

1 Answered Questions

[SOLVED] SQL Server - Export large table without primary key

  • 2016-01-29 12:39:54
  • no one
  • 1515 View
  • 9 Score
  • 1 Answer
  • Tags:   sql-server

1 Answered Questions

[SOLVED] Adding nullable column to table costs more than 10 minutes

1 Answered Questions

[SOLVED] Altering a table to add a New Column vs Create table with New Column

1 Answered Questions

[SOLVED] Allow self-referential foreign keys to be null from an alter-table

Sponsored Content