Let’s say you need to add a column to an existing table that already contains some rows. And this new column needs to be prepopulated with a default value. For example integer nullable column NEW_COLUMN is added to table MY_TABLE. Common approach is to check for column existence, and if it doesn’t exist – create and populate it:

if not (exists(SELECT * FROM SYSCOLUMNS WHERE ID = OBJECT_ID('[dbo].[MY_TABLE]') AND Name = 'NEW_COLUMN'))
BEGIN
	ALTER TABLE [dbo].[MY_TABLE] ADD [NEW_COLUMN] [int] NULL
	UPDATE [dbo].[MY_TABLE] SET [NEW_COLUMN] = 1
END

But this doesn’t work – you get “Invalid column name” error. The trick is – you don’t need a separate UPDATE statement. ALTER TABLE … ADD statement has a DEFAULT parameter. You can modify previous code like this:

if not (exists(SELECT * FROM SYSCOLUMNS WHERE ID = OBJECT_ID('[dbo].[MY_TABLE]') AND Name = 'NEW_COLUMN'))
BEGIN
	ALTER TABLE [dbo].[MY_TABLE] ADD [NEW_COLUMN] [int] NULL DEFAULT 1 WITH VALUES
END

This ALTER statement assigns default value of 1 to the new column. WITH VALUES parameter is important, it populates existing rows with the default value (without it it will be populated by NULLs).