Post a Comment On: Blogpad

Adding a new bit, not null column to an existing table requires you to specify a default value.

ALTER TABLE Users
ADD IsAvailable Bit not null default 0

It's better to give the default constraint a name, so we can use it later if we want to delete the column (the default constraint must be dropped before trying to delete the column)

ALTER TABLE Users
ADD IsAvailable bit NOT NULL CONSTRAINT [DF_Users_IsAvailable] DEFAULT 0

If we dont do that the system automatically assigns a random name for the constraint and it will be a bit more dificult to do the following:

ALTER TABLE Users
DROP CONSTRAINT DF_Users_IsAvailable

ALTER TABLE Users
DROP COLUMN IsAvailable

ALTER TABLE Users
ADD IsAvailable bit NOT NULL CONSTRAINT [DF_Users_IsAvailable] DEFAULT 1

if the system named the default constraint we can use the following query to find out that name (code from http://msmvps.com/blogs/robfarley/archive/2007/11/26/two-ways-to-find-drop-a-default-constraint-without-knowing-its-name.aspx)

declare @table_name nvarchar(256)
declare @col_name nvarchar(256)
set @table_name = N'Users'
set @col_name = N'IsAvailable'

select t.name, c.name, d.name, d.definition, d.*
from sys.tables t
join
sys.default_constraints d
on d.parent_object_id = t.object_id
join
sys.columns c
on c.object_id = t.object_id
and c.column_id = d.parent_column_id
where t.name = @table_name