There are two options for adding a “not null” column to a table using t-sql alter table statement in Microsoft SQL Server.

• Add a default to the new column, which sets the value of existing records to a not null value
• Add the column as a “null” column, set the value of existing records then change the column to a “not null” column

Both have there pros and cons, using a default may impact performance but is less code. using the other approach might be better for production performance but is more code and the new column must be supplied by inserts.

Using a default

use MyDatabase
go -- Add the column "MyColumn1" to table "MyTable" as "NOT NULL"
if exists (select 1 from sys.objects where name = 'MyTable' and type = 'U') 
begin 
 if not exists (select 1 from sys.columns where name = 'MyColumn1' and Object_ID = Object_ID('MyTable')) 
 begin 
   alter table MyTable with nocheck add MyColumn1 int not null default(0)   end
end go

Adding as “null”, then change to “not null”

use MyDatabase
go 
-- Add the column "MyColumn2" to table "MyTable" as "NULL"
if exists (select 1 from sys.objects where Object_ID = Object_ID('MyTable'))
begin if not exists (select 1 from sys.columns where name = 'MyColumn2' and Object_ID = Object_ID('MyTable'))
 begin 
   alter table MyTable with nocheck add MyColumn2 int null
 end
end go 
-- Change value of existing records 
if exists (select 1 from sys.objects where Object_ID = Object_ID('MyTable')) 
begin 
  if exists (select 1 from sys.columns where name = 'MyColumn2' and Object_ID = Object_ID('MyTable') and is_nullable = 1) 
  begin 
    update MyTable set MyColumn2 = 0
  end
end
go



-- Change "NULL" column to "NOT NULL" column
if exists (select 1 from sys.objects where Object_ID = Object_ID('MyTable'))
begin
  if exists (select 1 from sys.columns where name = 'MyColumn2' and Object_ID = Object_ID('MyTable') and is_nullable = 1)
  begin
    alter table MyTable alter column MyColumn2 int not null
  end
end
go

Note

If you’re table has a schema name use Object_ID(‘MySchema.MyTable’)

MSDN documentation

NULL / NOT NULL: Specifies whether the column can accept null values. Columns that do not allow null values can be added with ALTER TABLE only if they have a default specified. A new column added to a table must either allow null values, or the column must be specified with a default value.

http://msdn.microsoft.com/en-us/library/aa275462(SQL.80).aspx