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

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.

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

ALTER TABLE <table name> 
ADD <new column name> <data type> NOT NULL
GO
ALTER TABLE <table name> 
ADD CONSTRAINT <constraint name> DEFAULT <default value> FOR <new column name>
GO

ALTER TABLE MYTABLE ADD MYNEWCOLUMN VARCHAR(200) DEFAULT 'SNUGGLES'

Beware when the column you are adding has a NOT NULL constraint, yet does not have a DEFAULT constraint (value). The ALTER TABLE statement will fail in that case if the table has any rows in it. The solution is to either remove the NOT NULL constraint from the new column, or provide a DEFAULT constraint for it.

The most basic version with two lines only

ALTER TABLE MyTable
ADD MyNewColumn INT NOT NULL DEFAULT 0

-- add a column with a default DateTime
-- to capture when each record is added.

ALTER TABLE myTableName  
ADD RecordAddedDate smalldatetime NULL DEFAULT(GetDate())  
GO 

If you want to add multiple columns you can do it this way for example:

ALTER TABLE YourTable
    ADD Column1 INT NOT NULL DEFAULT 0,
        Column2 INT NOT NULL DEFAULT 1,
        Column3 VARCHAR(50) DEFAULT 'Hello'
GO

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

Msdn Link

In SQL Server 2008-R2, I go to the design mode -in a test db- and add my two columns using the designer and made the settings with the GUI then, the infamous Right-Click 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.

You can do the thing with T-SQL by 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 also by right clicking table in Design menu setting default value to table.

And futher more if you want to add same column(if not exists) to all tables in databse then use.

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

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]

To add a column to an existing database with a default value, we can use:

ALTER TABLE [dbo.table_name]
    ADD [Column_Name] BIT NOT NULL
Default ( 0 )

Here is another way to add a column to an existing database with a default value.

A much more thorough SQL script to add a column with a default value is below including checking if the column exists before adding it also checkin the constraint and dropping it if there is one. This script also names the constraint so we can have a nice naming convention (I like DF_) and if not SQL will give us a constraint with a name which has a randomly generated number; so it's nice to be able to name the constraint too.

-------------------------------------------------------------------------
-- Drop COLUMN
-- Name of Column: Column_EmployeeName
-- Name of Table: table_Emplyee
--------------------------------------------------------------------------
IF EXISTS (
            SELECT 1
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = 'table_Emplyee'
              AND COLUMN_NAME = 'Column_EmployeeName'
           )
    BEGIN

        IF EXISTS ( SELECT 1
                    FROM sys.default_constraints
                    WHERE object_id = OBJECT_ID('[dbo].[DF_table_Emplyee_Column_EmployeeName]')
                      AND parent_object_id = OBJECT_ID('[dbo].[table_Emplyee]')
                  )
            BEGIN
                ------  DROP Contraint

                ALTER TABLE [dbo].[table_Emplyee] DROP CONSTRAINT [DF_table_Emplyee_Column_EmployeeName]
            PRINT '[DF_table_Emplyee_Column_EmployeeName] was dropped'
            END
     --    -----   DROP Column   -----------------------------------------------------------------
        ALTER TABLE [dbo].table_Emplyee
            DROP COLUMN Column_EmployeeName
        PRINT 'Column Column_EmployeeName in images table was dropped'
    END

--------------------------------------------------------------------------
-- ADD  COLUMN Column_EmployeeName IN table_Emplyee table
--------------------------------------------------------------------------
IF NOT EXISTS (
                SELECT 1
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_NAME = 'table_Emplyee'
                  AND COLUMN_NAME = 'Column_EmployeeName'
               )
    BEGIN
    ----- ADD Column & Contraint
        ALTER TABLE dbo.table_Emplyee
            ADD Column_EmployeeName BIT   NOT NULL
            CONSTRAINT [DF_table_Emplyee_Column_EmployeeName]  DEFAULT (0)
        PRINT 'Column [DF_table_Emplyee_Column_EmployeeName] in table_Emplyee table was Added'
        PRINT 'Contraint [DF_table_Emplyee_Column_EmployeeName] was Added'
     END

GO

These are two ways to add a column to an existing database with a default value.

ALTER TABLE ADD ColumnName {Column_Type} Constraint

The MSDN article ALTER TABLE (Transact-SQL) has all of the alter table syntax.

Example:

ALTER TABLE [Employees] ADD Seniority int not null default 0 GO

Example:

ALTER TABLE tes 
ADD ssd  NUMBER   DEFAULT '0';

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

ALTER TABLE Product 
ADD ReferenceID uniqueidentifier not null 
default (cast(cast(0 as binary) as uniqueidentifier))

This can also be done in the SSMS GUI. I showed a default date below but, the default value can be whatever, of course.

1. Put your table in design view (Right click on the table in object explorer->Design)
2. Add a column to the table (or click on the column you want to update if it already exists)
3. In Column Properties below, enter (getdate()) or abc or 0 or whatever value you want in Default Value or Binding field as pictured below

Try this

ALTER TABLE Product
ADD ProductID INT NOT NULL DEFAULT(1)
GO

Add new column to Table

ALTER TABLE [table]
ADD Column1 Datatype

E.g

ALTER TABLE [test]
ADD ID Int

If User wants to make it auto incremented then

ALTER TABLE [test]
ADD ID Int IDENTITY(1,1) NOT NULL

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

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.

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

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

This can be done by below code.

 CREATE TABLE TestTable
    (FirstCol INT NOT NULL)
    GO
    ------------------------------
    -- Option 1
    ------------------------------
    -- Adding New Column
    ALTER TABLE TestTable
    ADD SecondCol INT
    GO
    -- Updating it with Default
    UPDATE TestTable
    SET SecondCol = 0
    GO
    -- Alter
    ALTER TABLE TestTable
    ALTER COLUMN SecondCol INT NOT NULL
    GO

First create a table with name student:

CREATE TABLE STUDENT ( STUDENT_ID INT NOT NULL)

add 1 column to it:

ALTER TABLE STUDENT ADD STUDENT_NAME INT NOT NULL DEFAULT(0)


SELECT * FROM STUDENT

Table created and a column is added to an existing table with a default value.

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!