Add column with default value to existing table in sql server

In this tutorial i am going to explain about Add column with default value to existing table in sql server. For more details about default values to columns you may refer this link. In my previous tutorial i have explained about

Implementation :

Now we will see the implementation of how to add column with default value to existing table in sql server.  The example used here may be silly. This is just for explanation purpose only.

First open the sql server management studio, choose your database and create a new table called Customer. Below i have given the query which i used to create customer table.

Once the table is created insert some sample records in the table.

Now if you select the records from the Customer table you will see the below results.

Step 1 of Add column with default value to existing table in sql server

Add column with default value to existing table in sql server:

Till now we have created a sample table and inserted some records. Now let’s see how to add column with default value to existing table in sql server. To add the column with default constraint to an existing table, below syntax will be used.

So using the above syntax i have written the query to add CreatedDate column to Customer table with default constraint named as Default_CrDate which accepts GETDATE() as the default value. Below is my query.

Now if you run the select query on customer table you can see the CreatedDate column with default value as current date.

Now run sp_help Customer query you will get all table related information. And you can see the constraint created on CreatedDate column. Refer the below screenshot.

In the syntax we used it is optional to mention the constraint name. In case if we didn’t mention the constraint name then it will auto create some name to the constraint. Lets check how it works.

To check this i am dropping the CreatedDate column from the customer table and recreating it without specifying the constraint name. Below is the query to drop the column. Since the column has constraint we need to drop the constraint first and then only we can drop the column.

Now run the below query to recreate the CreatedDate column.

Now if you run sp_help Customer query you can see the Default constraint created in CreatedDate column but the constraint name here is DF__Customer__Create__15502E78 which is auto generated by the server.

Whatever the constraint name who cares? No one. The reason we explicitly name the constraint is to make it meaningful and also to follow some uniform coding practice across.

Conclusion:

Hope you learned how to add column with default value to existing table in sql server. Do you like this tutorial? Help us to improve. Please post your comments and feedback below.

Subscribe For Latest Updates

Signup for our newsletter and get notified when we publish new articles for free!