MS SQL Server

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

There are four major tasks that precisely describe what a database developer does: Create, Read, Update, and Delete. As a developer, you will need to add and change columns multiple times.

In this brief article, we will learn how to add a column with a default value to an existing table in the SQL Server.

SQL Server Default Constraint

As the name suggests, the default constraint defines a default value for a specific column. If we provide no value for the column, the SQL Server will use the specified value instead of null.

This is mainly useful when combined with other constraints, such as not null.

In most cases, you set the default constraint when creating a table. However, you can add a column with default value. Doing this affects the data that is inserted after the default constraint.

Add a Column With a Default Value

Assume we have a table as shown in the following query:

usetemporary_db;
createtablesample_table(
    idintnotnullidentity(1,1) primary key,
    namevarchar(50),
);
insertintosample_table(name)
values ('Abigail Henderson'),
       ('Peter Miller'),
       ('Harris Gonzales'),
       ('Anne Jenkins'),
       ('Katherine Patterson');
select * fromsample_table;

To add a column with a default value, we can use the alter table query as shown below:

alter table sample_table add with_default_col varchar(50) default 'John Doe';

If we insert new data, any missing value for the specified column will be replaced with the set default value:

set identity_insert sample_table ON;

insert into sample_table(id) values (7);

select * from sample_table;

The previous queries should return a result set as shown:

Notice only the new inserted record contains the default value for the specified column.

Add Column Default Value of All Inserts

To solve the problem, where all existing values are set to null, we need to use them with values clause as shown:

alter table sample_table

add another_column int default 1

with values;

If we insert records with no values for the another_column provided, we should get the result as:

Notice that the existing columns are replaced with the default value.

Conclusion

In this article, we discovered how to add a column with a default value to an existing table in the SQL Server. Plus, we discussed the SQL server default constraint, adding a column with a default value, and adding a column with a default value of all inserts. We hope you found this article helpful. Check out the other Linux Hint articles for more SQL Server tutorials.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list