I have a fairly large table (approx 400 million rows) and I need to add new column of type bit with NOT NULL constraint. My approach is to add the field without NOT NULL constraint, set the field to 0 and the alter the column to add the NOT NULL constraint. This is a pretty slow process. Is there a quicker approach?

I am on SQL Server 2014 Enterprise Edition

  • Dupe of stackoverflow.com/questions/1645215/…
    – dfundako
    Commented Aug 17, 2016 at 16:04
  • SQL Server 2014 Enterprise
    – johnmcp
    Commented Aug 17, 2016 at 17:33
  • 3
    Question is 7 years old and clearly there are new possibilities now
    – johnmcp
    Commented Aug 17, 2016 at 19:49
  • 1
    @johnmcp Yes, that question is a bit old and none of the answers reflected the new info. I have corrected that ;) Commented Aug 17, 2016 at 20:03

1 Answer 1


As you are on Enterprise Edition and version >= 2012 then the information in Online non-NULL with values column add in SQL Server 2012 should apply.

Alter table yourtable 
    add newcolumn bit not null default 0;

Should be metadata only and, unlike your proposed solution, will not have to write anything to all rows.

  • Excellent, this is exactly what I need. Thanks a lot.
    – johnmcp
    Commented Aug 17, 2016 at 19:48
  • 2
    @johnmcp and Martin: The WITH VALUES clause is not needed here. That is only for adding a NULLable column that has a default and you want the initial value to be the default instead of NULL. For this particular case, only the DEFAULT is needed (and set to 0 instead of 1 ;). Commented Aug 17, 2016 at 19:49
  • @johnmcp So the answer to this question was the same answer to the question that was 9 years old?
    – dfundako
    Commented Aug 17, 2016 at 20:40
  • @dfundako nope. The online default values was only introduced in 2012. It wasn't mentioned in that other question until srutzky gave an answer just now. Commented Aug 17, 2016 at 20:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.