Add a column with a default value to an existing table in SQL Server

SQL Server + Alter Table + Add Column + Default Value uniqueidentifier...

ALTER TABLE [TABLENAME] ADD MyNewColumn INT not null default 0 GO

ALTER TABLE Protocols
ADD ProtocolTypeID int NOT NULL DEFAULT(1)
GO

The inclusion of the **DEFAULT** fills the column in **existing** rows with the default value, so the NOT NULL constraint is not violated.

ALTER TABLE tbl_table ADD int_column int NOT NULL DEFAULT(0)

From this query you can add a column of datatype integer with default value 0.

IF NOT EXISTS (
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='TABLENAME' AND COLUMN_NAME = 'COLUMNNAME'
)
BEGIN
ALTER TABLE TABLENAME ADD COLUMNNAME Nvarchar(MAX) Not Null default
END

If the default is Null, then:

1. In SQL Server, open the tree of the targeted table
2. Right click "Columns" ==> `New Column`
3. Type the column Name, `Select Type`, and Check the Allow Nulls Checkbox
4. From the Menu Bar, click `Save`

Done!

When adding a *nullable column*, `WITH VALUES` will ensure that the specific DEFAULT value is applied to existing rows:

ALTER TABLE table
ADD column BIT -- Demonstration with NULL-able column added
CONSTRAINT Constraint_name DEFAULT 0 WITH VALUES

You can do the thing with T-SQL in the following way.

ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}

As well as you can use [SQL Server Management Studio][1] also by right clicking table in the Design menu, setting the default value to table.

And furthermore, if you want to add the same column (if it does not exists) to all tables in database, then use:

USE AdventureWorks;
EXEC sp_msforeachtable
'PRINT ''ALTER TABLE ? ADD Date_Created DATETIME DEFAULT GETDATE();''' ;

[1]:

[To see links please register here]

In SQL Server 2008-R2, I go to the design mode - in a test database - and add my two columns using the designer and made the settings with the GUI, and then the infamous <kbd>Right-Click</kbd> gives the option "***Generate Change Script***"!

Bang up pops a little window with, you guessed it, the properly formatted guaranteed-to-work change script. Hit the easy button.

Use:

ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}

Reference: [ALTER TABLE (Transact-SQL)][1] (MSDN)

[1]:

[To see links please register here]

Alternatively, you can add a default without having to explicitly name the constraint:

ALTER TABLE [schema].[tablename] ADD DEFAULT ((0)) FOR [columnname]

If you have an issue with existing default constraints when creating this constraint then they can be removed by:

alter table [schema].[tablename] drop constraint [constraintname]