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)
OR use ‘NOT NULL’ while adding column.
ALTER TABLE myTable
ADD newCol VARCHAR(10) NOT NULL DEFAULT ‘DefValue’
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’