Alter query to add column with default value in SQL Server

Introduction:

In this article I will explain how to write Query to add new column with default value to existing datatable in SQL server.

Description:

In previous post I explained how to alter or add new column in datatable using SQL Server. Now I will explain how to write query to add new column with default value to existing datatable in SQL Server. During work with one application I got requirement like alter existing table structure to add new column with default value 0(zero). If we want to add new column with some default value to existing table we need to write the query in a format as shown below

Query to add new column with default value to existing table

If we want to add new column with default value to existing table that syntax will be like this

ALTER TABLE TABLE_NAME ADD COLUMN_NAME DATATYPE NULL|NOT NULL CONSTRAINT CONSTRAINT_NAME DEFAULT DEFAULT_VALUE

Ex:

ALTER TABLE Employee_Details ADD isacitve INT NULL CONSTRAINT DF_TABLE_COL_ISACTIVE DEFAULT '0'

From the above query declaration new column “isacitve” will add to Employee_Details with data type INT and with Default value “0”

In above query I declared CONSTRAINT_NAME without declare CONSTRAINT_NAME also we can add column with default value. If we declare CONSTRAINT_NAME it will take the name what we define otherwise automatically it will create constraint name that would be like this

Query to add new column with default value without constraint name

ALTER TABLE TABLE_NAME ADD COLUMN_NAME DATATYPE NULL|NOT NULL DEFAULT DEFAULT_VALUE

Ex:

ALTER TABLE Employee_Details ADD isacitve INT NULL DEFAULT '0'

If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS Subscribe by Email