SQL SERVER – Adding Default Value to Existing Table

While my primary focus is on SQL Server Performance Tuning, I often get many different questions during the Comprehensive Database Performance Health Check. Today we will see questions related to adding default value to the existing table.

One of my clients recently asked during the health check work if I know a way to add a column with a default to the table. Of course, I know as there is a T-SQL for it.

SQL SERVER - Adding Default Value to Existing Table default-value0-800x397

Setup Sample Table

First, let us set up a sample table.

USE tempdb
GO
CREATE TABLE myTable (ID INT)
GO
INSERT INTO myTable (ID)
VALUES (1), (2), (3)
GO
SELECT *
FROM myTable
GO

When you run the above query you will get the following results.

SQL SERVER - Adding Default Value to Existing Table default-value1

Method 1: Add Column with Default for Future Inserts

You can easily add a column with the default for the table by running the script here.

ALTER TABLE myTable
ADD newCol VARCHAR(10) DEFAULT 'DefValue'
GO

However, when you run the script above it will add a column containing the NULL value.

INSERT INTO myTable (ID)
VALUES (4)
GO

However, whenever you insert the new value in the table, it will contain the default.

SQL SERVER - Adding Default Value to Existing Table default-value2

This often confuses many new users as most of the time when they run the query, they expect that all the new columns will have the default. If you want your newly added column to have a default value as well, you can follow method 2.

SQL SERVER - Adding Default Value to Existing Table default-value3

Method 2: Add Column with Default Value for All Inserts

As mentioned in the previous method, the alter table script will add a new column but the new column will contain the NULL values. If you want your newly added column to contain the default, you can use the following script.

ALTER TABLE myTable
ADD newColWithVal VARCHAR(10) DEFAULT 'DefValue'
WITH VALUES
GO

The following script will add a new column with a default value and all the future inserts (when the column is not populated) will also contain the default value.

SQL SERVER - Adding Default Value to Existing Table default-value4

Well, that’s it today. Remember there is no question small question. If you do not know the answer, always ask.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Creating System Admin (SA) Login With Empty Password – Bad Practice
Next Post
What is the Cloud?

Related Posts

2 Comments. Leave new

  • Sanjay Monpara
    June 29, 2020 5:34 pm

    OR use ‘NOT NULL’ while adding column.

    ALTER TABLE myTable
    ADD newCol VARCHAR(10) NOT NULL DEFAULT ‘DefValue’
    GO

    Reply
  • We can add new column for existing data with default values by using below query with out ‘With Values’

    ALTER TABLE myTable
    ADD NewColWithNotNull VARCHAR(10) NOT NULL DEFAULT ‘DefValue’

    Reply

Leave a Reply

Menu