MS Sql Server: Alter Table set default value

Hi Friends,

Do you want to know how to Alter Table for adding new column with Default value? The sysntax of sql server is:

ALTER TABLE table
{
ADD
{
column_name data_type [ ( size ) ]
[ DEFAULT value ]
{ [ NULL | NOT NULL ] | [ { PRIMARY KEY | UNIQUE } ] }
{ [ CONSTRAINT constraint_name ] }
}
|
ALTER COLUMN column_name
{
data_type [ ( size ) ] [ ( precision [ , scale ] ) ]
[ NULL | NOT NULL ]
}
|
DROP { COLUMN column_name | [ CONSTRAINT ] constraint_name }
}

table

Specifies which table is to be altered.

column_name

The name of the column being added, altered, or dropped.

data_type

The data type of the column being added or altered.

size

Is the length of the data that can be entered into a field.

DEFAULT value

Is the default value for the column being altered.

NULL | NOT NULL

Is a parameter that indicates whether a column can or cannot contain null values.

PRIMARY KEY

Is a parameter that identifies the column or set of columns whose values uniquely identify each row in a table. Each table can only have one primary key constraint.

UNIQUE

Is a constraint that enforces the uniqueness of the values in a set of columns.

constraint_name

The name of the constraint to be added or dropped.

precision

Specifies the precision for the data type.

scale

Specifies the scale for the data type.

The ALTER TABLE statement can be used to modify an existing table by adding, altering, or dropping columns and indexes.

Reference: http://www.devguru.com/technologies/t-sql/7120.asp

So when you want to set the default value when you are adding a new column in the databse table, you can use the following example. I have created a column named ‘tempTest’ and its datatype is ‘bit’ and default value is ‘0’ i.e. false.

ALTER TABLE tblTable
ADD isTempTest bit NOT NULL
CONSTRAINT [DF_tblTable_isTempTest] DEFAULT ((0))

I have added CONSTRAINT ‘DF_tblTable_isTempTest’ to set the default value 0.

You can add you comment here.