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

Syntax:

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

Example:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate
    a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:
The WITH VALUES is only needed when your Column is Nullable
    and you want the Default Value used for Existing Records.
If your Column is NOT NULL, then it will automatically use the Default Value
    for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable and do not Specify SomeCol's value, then it will Default to 0.
If you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls),
    then the Default-Constraint will not be used and NULL will be inserted as the Value.

Notes were based on everyone's great feedback below.
Special Thanks to:
    @Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.

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 <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

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.

Use:

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

ALTER TABLE myTableName  
ADD RecordAddedDate SMALLDATETIME NULL DEFAULT (GETDATE())  
GO 

ALTER TABLE ADD ColumnName {Column_Type} Constraint

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

First create a table with name student:

CREATE TABLE STUDENT (STUDENT_ID INT NOT NULL)

Add one column to it:

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

SELECT * 
FROM STUDENT

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

--Adding Value with Default Value
ALTER TABLE TestTable
ADD ThirdCol INT NOT NULL DEFAULT(0)
GO

This is for SQL Server:

ALTER TABLE TableName
ADD ColumnName (type) -- NULL OR NOT NULL
DEFAULT (default value)
WITH VALUES

Example:

ALTER TABLE Activities
ADD status int NOT NULL DEFAULT (0)
WITH VALUES

If you want to add constraints then:

ALTER TABLE Table_1
ADD row3 int NOT NULL
CONSTRAINT CONSTRAINT_NAME DEFAULT (0)
WITH VALUES

Try with the below query:

ALTER TABLE MyTable
ADD MyNewColumn DataType DEFAULT DefaultValue

This will add a new column into the Table.

There are 2 different ways to address this problem. Both adds a default value but adds a totally different meaning to the problem statement here.

Lets start with creating some sample data.

Create Sample Data

CREATE TABLE ExistingTable (ID INT)
GO
INSERT INTO ExistingTable (ID)
VALUES (1), (2), (3)
GO
SELECT *
FROM ExistingTable

1.Add Columns with Default Value for Future Inserts

ALTER TABLE ExistingTable
ADD ColWithDefault VARCHAR(10) DEFAULT 'Hi'
GO

So now as we have added a default column when we are inserting a new record it will default it's value to 'Hi' if value not provided

INSERT INTO ExistingTable(ID)
VALUES (4)
GO
Select * from ExistingTable
GO

Well this addresses our problem to have default value but here is a catch to the problem. What if we want to have default value in all the columns not just the future inserts??? For this we have Method 2.

2.Add Column with Default Value for ALL Inserts

ALTER TABLE ExistingTable
ADD DefaultColWithVal VARCHAR(10) DEFAULT 'DefaultAll'
WITH VALUES
GO
Select * from ExistingTable
GO

The following script will add a new column with a default value in every possible scenario.

Hope it adds value to the question asked. Thanks.

ALTER TABLE <YOUR_TABLENAME>
ADD <YOUR_COLUMNNAME> <DATATYPE> <NULL|NOT NULL> 
ADD CONSTRAINT <CONSTRAINT_NAME>   ----OPTIONAL
DEFAULT <DEFAULT_VALUE>

If you are not giving constrain name then sql server use default name for this.

Example:-

ALTER TABLE TEMP_TABLENAME
ADD COLUMN1 NUMERIC(10,0) NOT NULL
ADD CONSTRAINT ABCDE   ----OPTIONAL
DEFAULT (0)

step-1. FIRST YOU HAVE TO ALTER TABLE WITH ADD a FIELD

alter table table_name add field field_name data_type

step-2 CREATE DEFAULT

USE data_base_name;
GO
CREATE DEFAULT default_name AS 'default_value';

step-3 THEN YOU HAVE TO EXECUTE THIS PROCEDURE

exec sp_bindefault 'default_name' , 'schema_name.table_name.field_name'

example -

USE master;
GO
EXEC sp_bindefault 'today', 'HumanResources.Employee.HireDate';

OFFLINE and ONLINE pertain to how to ALTER table performed on NDB Cluster Tables. NDB Cluster supports online ALTER TABLE operations using the ALGORITHM=INPLACE syntax in MySQL NDB Cluster 7.3 and later. NDB Cluster also supports an older syntax specific to NDB that uses the ONLINE and OFFLINE keywords. These keywords are deprecated beginning with MySQL NDB Cluster 7.3; they continue to be supported in MySQL NDB Cluster 7.4 but are subject to removal in a future version of NDB Cluster.

IGNORE pertains to how the ALTER statement will deal with duplicate value in the column that has newly added constraint UNIQUE. If IGNORE is not specified, ALTER will fail and not be applied. If IGNORE is specified, the first row of all duplicate rows is kept, the reset deleted and the ALTER applied.

The ALTER_SPECIFICATION would be what you are changing. what column or index you are adding, dropping or modifying, or what constraints you are applying on the column.

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
       alter_specification [, alter_specification] ...

    alter_specification:
        ...
        ADD [COLUMN] (col_name column_definition,...)
        ...

Eg: ALTER TABLE table1 ADD COLUMN foo INT DEFAULT 0;

In SQL Server, you can use below template:

ALTER TABLE {tablename}
ADD 
    {columnname} {datatype} DEFAULT {default_value}

For example, to add a new column [Column1] of data type int with default value = 1 into an existing table [Table1] , you can use below query:

ALTER TABLE [Table1]
ADD 
    [Column1] INT DEFAULT 1

ALTER table dataset.tablename ADD column_current_ind integer DEFAULT 0

SYNTAX:

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

EXAMPLE:

ALTER TABLE Admin_Master 
ADD Can_View_Password  BIT NULL 
CONSTRAINT DF_Admin_Master_Can_View_Password DEFAULT (1)
WITH VALUES