How to create a table with default value in SQL Server

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

Back to Basics series: Default Values in SQL Server.

Introduction


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

Neerajprasadsharma
Full Name: NEERAJ Sharma
Member Level: Bronze
Member Status: Member
Member Since: 5/13/2016 8:42:37 AM
Country: India
I write technical articles mainly on SQL Server Query Optimizer for more information look at my BIO.

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: https://www.sqlshack.com/author/neeraj/ https://www.codeproject.com/script/Articles/MemberArticles.aspx?amid=12524731 https://www.mssqltips.com/sqlserverauthor/243/neeraj-prasad-sharma-/

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)