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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.