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 Aug 17 '16 at 16:04
  • SQL Server 2014 Enterprise – johnmcp Aug 17 '16 at 17:33
  • 3
    Question is 7 years old and clearly there are new possibilities now – johnmcp Aug 17 '16 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 ;) – Solomon Rutzky Aug 17 '16 at 20:03

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 Aug 17 '16 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 ;). – Solomon Rutzky Aug 17 '16 at 19:49
  • @johnmcp So the answer to this question was the same answer to the question that was 9 years old? – dfundako Aug 17 '16 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. – Martin Smith Aug 17 '16 at 20:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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