How to Add a Column with a Default Value to an Existing Table in SQL Server

In SQL Server sometimes it may be required to set the default value to a Column in a table. This default value will be set as the column value when there is no value inserted or left empty for that particular column while data is inserted into the Table. Sometimes after creating a table, it may be required to add a New Column with Default Value based on new requirements or the developer missed it to add a specific Column when the database table schema was initially created.

In this article, we will discuss about adding a New Column with a Default value to an existing Table in SQL Server.

Adding a New Column with a Default Value to an existing Table

Adding a New Column with a Default value to an existing Table means, that after creating a Table, how a new Column is added to the table with a Default Value, and the default value data type should be based on the Data Type of the new Column added. Adding a New Column with a default value into an existing table can be done using two methods.

1. ALTER TABLE
2. SSMS Method

Let us look into both methods in detail with examples below:

1. ALTER TABLE

Alter table method can be used with the T-SQL statement from the Query Window in SQL Management Studio to add aa New Column with a Default value to an existing Table. The New Column added can be of different data types and the default value set should be of the same data type as the new column added. There are different data types like bit, numerical, Date, or Text. So we will see how different data type columns can be added with default value using the ‘Alter Table’ command.

Below is the Syntax to add a new column with default value to an existing Table:

Syntax:

ALTER TABLE {Table Name}

ADD {Column Name} {Data Type}

CONSTRAINT {Constraint Name}

DEFAULT {Default Value}

The ‘CONSTRAINT’ option is Optional and if it is left out, a default constraint name will be created by the SQL Server internally.

Below is the table ‘EmployeesData’, we are using to demonstrate the methods for adding a new column with default values to an existing table.

In all the examples ‘EmployeesData’ table is used and below is the create table query which shows the initial columns and its data types.

Create EmployeesData Table SQL Script

Create Table EmployeesData
(EmployeeID int NOT NULL IDENTITY(1,1),
EmployeeName Varchar(100),
Gender Varchar(10),
)
GO

Initally three rows of data is inserted for explanation purpose.

Insert Into EmployeesData Values ('Binny Dev','Male')
Insert Into EmployeesData Values ('Thiurai Raja','Male')
Insert Into EmployeesData Values ('Kukesh Rajan','Male')

Below is the out put with the data inserted for all existing columns. EmployeeID is a Idenity column and so the ID will be automatically inserted.

Output:

EmployeesData Table Initial values

Explained below about adding a new column into an existing Table with default value of different data types like BIT, DATE,TEXT and INT .

BIT Column Type with Default Value

BIT column type is True / False datatype which will hold values of 0 or 1 only which can be interpreted as TRUE / FALSE or YES/NO in front end application.

Below is an example of how a new Column with BIT data type and default value is added to existing ‘EmployeesData’ table.

ALTER TABLE [EmployeesData] ADD  [PreferWFO] BIT NOT NULL  DEFAULT (1)

Example Insert query:

Insert into Employee (EmployeeName,Gender) Values  ('Madan Mohan','Male')

In the above Insert query, values for EmployeeName and Gender are given. No value for PreferWFO is given, but since default 1 is set, this value is inserted to the column of the new record by default.

Output:

BIT Column added with data

The above example shows the new BIT column added and the data with default value 1 added to the BIT field.

Column CONSTRAINT

When adding a new column to an existing table with default value the CONSTRAINT Name is optional. Let us see below an example of how the CONSTRAINT name is used with Default Value.

Date Column with Default Value and Constraint name

When we are inserting a new record sometimes the date field may be inserted with today’s date. So a default value for the column set can be useful instead of inserting the date value each time. So when adding a New Column of type Date the Constraint name can be given for the Default Value. In the below example we are adding a new column with default value and giving CONSTRAINT name explictly (CS_DateOfJoinNewEmp) which will be unique to the database.

ALTER TABLE [EmployeesData] ADD  [DateJoined]  Date 
CONSTRAINT [CS_DateOfJoinNewEmp]  
DEFAULT (getdate()) 

Below is the example Insert Query which will insert the current date if no specific date value given during insert data.

Insert Into EmployeesData  (EmployeeName,Gender,PreferWFO) Values ('Praveena Mohan','Female',0)

In the above insert query example no date was given for the date column ‘DateJoined’, but the current date was added since the default value for this column is set as current date as shown below:

Output:

Date Column with default date value

In the above image you can see the new date column ‘DateJoined’ is added to the existing table and how the default date value inserted when new row was inserted.

Numeric Column with Default Value

The Numeric data type column will hold Integer data types such as INT, BigINT, SmallINT, TinyINT and Decimal Value data types such as DECIAL, NUMERIC , FLOAT, REAL.

Below is an example of how to add a new INT Column ‘SalaryOffered’ with default value to existing ‘EmployeesData’ table.

ALTER TABLE [EmployeesData] ADD  [SalaryOffered] INT DEFAULT (25000)

Example Insert query:

Insert into Employee (EmployeeName,Gender,DateJoined) Values ('Niraml Kumar','Male','2024/01/24')

When a new employee record is added to the employee, the SalaryOffered for new joinees is set as 25000 as an example. So in the above Insert Query the ‘SalaryOffered’ column is left out from insert columns. Since we have set the default value, the amount ‘25000’ is added when a new row is inserted to the table. Below output shows the result:

Output:

New INT column and default value in table

2. SSMS Method

A new column with Default Value can also be added to an existing table from SQL Server Management Studio (SSMS) using ‘New Column’ menu Option from object explorer window as below. In this example we are adding a new Text column ‘RoleAssigned’ with default value to EmployeeData table.

Steps for for Adding a New Column with default value to an existing table from SSMS as below:

1. Goto Object Explorer in SSMS

Object Explorer

2. Select Databases and click the + On the left.

Databases

This will list all avilable databases. Here we will select the database ‘DemoDB’

3. Click the ‘+’ left of ‘DemoDB’. This will list all database objects available in the selected database.

Database Objects

4. Click on the ‘+’ left of Tables object.

This will list all Tables available in the database.

Here we will select the Table name ‘EmployeesData’ and click the ‘+’ on the left of this table.

This will list all the table objects like Columns, Keys, Constraints, Triggers, Indexes and Statistics.

Table Objects

5. Click on the + left of Columns object under the EmployeesData table.

This will display all Columns created under EmployeesData Table.

6. Right click on the ‘Columns’ object and this will display the popup menu with menu option ‘New Column’.

Add New Column

7. Select the menu ‘New Colum’ from the popup menu.

This will display the window with options to edit the columns in the Employeesdata table.

Input the new column name ‘RoleAssigned’ and select data type ‘varchar(50)’ as below

Enter new Column Details

8. Select this new Column ‘RoleAssigned’ and go to the ‘Column Properties’ section below.

Add ‘Role Not Assigned’ aginst ‘Default Value or Binding’ under the properties section.

Column Properties – default value

9. Goto ‘Save’ icon on top menu and click to save all the changes done to create the new text column ‘RoleAssigned’

10. Right click on the ‘Columns’ object under the EmployeesData table and select ‘Refresh’. You can see the new column ‘RoleAssigned’ which we have created.

All New columns display

Conclusion

In this article we have seen how a new Column is created in an existing table with Default Value. We also explored the two methods, using the T-SQL method using ALTER TABLE method and also the SSMS method using the Object explorer option and using the ‘New Column’ menu option under the Coumns object inside the tables object. Adding new columns using the Alter Table method is mainly useful when there is already data added to the table. If no data is added to the table then delete the table and re-create the atble with the addditional columns and default values is the best option.