Add a column to an existing table in SQL Server

Altering a table is common situation during development. Sometimes it create some trouble. So Here I describing how to add a column to an existing table in SQL Server

Here Table structure

CREATE TABLE tblStudent
(
 Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
 StudentName VARCHAR(50) NOT NULL DEFAULT('')
)


Here,  Adding a column in email to existing tblStudent. To achieve this alter the table tblStudent
Syntax
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
[WITH VALUES]

Example-
ALTER table to add a column with DEFAULT

ALTER TABLE tblStudent
ADD Email VARCHAR(50) NOT NULL DEFAULT('')

ALTER table to add a column with DEFAULT and  CONSTRAINT

ALTER TABLE tblStudent
ADD Contact VARCHAR(50)
CONSTRAINT cntContact DEFAULT 0 WITH VALUES