SQL Server - Add a NOT NULL column to an existing table

Even if the DB is designed at first time, we may need to alter some tables and add new columns or at least to change the length of them.

When a NOT NULL column to be inserted into an existing table, the following error will be thrown,

Msg 4901, Level 16, State 1, Line 1

ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'Column1' cannot be added to non-empty table 'Table1' because it does not satisfy these conditions.

But as a short way, a nullable column can be added and then update with a value and then make it not null. See the example below,

 if not exists(select 1 from syscolumns where name='Column1' and object_name(id) = 'Table1')  
      alter table Table1 add Column1 dtDateTime null   
      go  
      update Table1 set Column1 = getdate()  
      go  
      alter table Table1 alter column Column1 dtDateTime not null       
 Go  

This will have the a NOT NULL column but be sure you have no big number of records in the table as it updates all and will take time with the number of rows in table.

Read more on MSDN here.

Happy Coding... :)