Ask a Question
Ask Question Login
Corporate Training
  1. Community
  2. SQL Server
  3. Question
SQL Server

How to Increase speed altering column on a large table to NON-NULL?

Asked by Ankur Vaish Apr 23, 2021 1.6K views 1 answer
Share

About this question

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.



Your answer

1 Answer

More SQL Server discussions

Learn & Explore

Free tutorials and interview questions from industry experts — learn the skill, then get ready to prove it.

Latest SQL Server Blogs

Guides, tips and career advice on SQL Server from JanBask experts.