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.

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.

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.

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.

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.

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)