How to create a table with default value in SQL Server

Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 42126 red flag

Back to Basics series: Default Values in SQL Server.


How to create or alter existing or drop Default column in SQL Server.

How to create a table with default value in SQL Server
In SQL Server we can create a default column, which inserts predefined values, if not provided in SQL statement.
Let us jump to the topic and do some practical example to understand it better.

Create Default column Syntax:
Create Table YourTable_Name (yourColumn_Name1 DataType Constraint Const_Name Default (DefaultValue) , yourColumn_Name2 DataType )

Let`s create a table using this syntax:

 Create Table TableWithdefaultvalue (ID Int Constraint DF_ID DEFAULT(0) , name Varchar(10) , Country Varchar(10) )

In the above example, we have an ID column which will accept the value 0 (zero) as default value in TableWithdefaultvalue table. Default constraint works with all data types, let us try again, but this time with Varchar data type.

Create Table TableWithdefaultvalueVarchar (ID Int ,name Varchar(10) ,Country Varchar(10) constraint DF_Name default 'India' )

It is also possible to create a default constraint to an existing table using ALTER command.

Alter Table Add Default Constraint Syntax:
Alter Table Table_Name Add Constraint Const_Name Default(Default_Value) For Column_Name

Let us use the above syntax in the example below:

Alter table TableWithdefaultvalueVarchar add CONSTRAINT DEF_ID   default(1) for ID

In the above example, we are adding default value "0" for ID column. We can also drop default constraint if we don`t want it to use in the table using below syntax.

Drop (Default) Constraint Syntax:
Alter Table TableName Drop Constraint ConstraintName

Let us use the above syntax to drop "DF_ID" constraint of the TableWithdefaultvalue  table.

Alter table TableWithdefaultvalue drop CONSTRAINT DF_ID

Test What We Learnt above

In this tutorial, we learnt that how we can set a column to default value, so let us put some data in the table and see the result.

insert into TableWithdefaultvalueVarchar (name) values ('Neeraj')
select * From TableWithdefaultvalueVarchar

We have only inserted data for NAME column. But we can see the values for ID and Country columns as well because those are Default values for the respective columns.
Default column doesn`t mean that default column will only contain predefined values, we can insert data in those columns as well see below:

insert into TableWithdefaultvalueVarchar values (2,'Tom' , 'England')
select * From TableWithdefaultvalueVarchar

Page copy protected against web site content infringement by Copyscape

About the Author

Full Name: Neeraj Prasad Sharma
Member Level: Bronze
Member Status: Member
Member Since: 5/13/2016 8:42:37 AM
Country: India
Contact for Free SQL Server Performance Consulting and Training for you or your Organization.

Neeraj Prasad Sharma is a SQL Server developer who started his work as a dot net programmer. He loves SQL Server query optimizer`s capability to process the queries optimally. For the last six years he has been experimenting and testing Query Optimizer default behaviour and if something goes wrong his goal is to identify the reason behind it and fix it. I write technical article here:

Login to vote for this post.

Comments or Responses

Posted by: Reze12 on: 4/18/2019 | Points: 25
thanks for the information. its really very helpful.

Login to post response

Comment using Facebook(Author doesn't get notification)