SQL SERVER - Add New Column In Existing Table With Default Value

Many times, we need to add new column in our existing database table. Many of us prefer to use a table designer to add new column, I would like to use SQL query to do this operation. If we add new column through designer it will not update the default value for that column and we need to update that column. With the help of following query, we can add new column to our existing table with default value we want to assign to that column without executing update statement.

ALTER TABLE [dbo].[Account]
ADD Active BIT DEFAULT 1 WITH VALUES;

Following is the complete example to test the above SQL statement.

USE [Master]
GO
CREATE DATABASE MyTestDB
GO

USE MyTestDB
GO
CREATE TABLE [dbo].[Account](
 [AccountID] INT IDENTITY(1,1) PRIMARY KEY,
 [AccountNumber] [varchar](6) NULL,
 [BranchName] [varchar](6) NULL,
 [Balance] [numeric](8, 2) NULL
) 
GO

INSERT INTO dbo.Account(AccountNumber,BranchName,Balance)
SELECT '000001','Br001',1202.00
UNION SELECT '000002','Br002',904.00

SELECT * FROM dbo.Account

ALTER TABLE [dbo].[Account]
ADD Active BIT DEFAULT 1 WITH VALUES;

SELECT * FROM dbo.Account

DROP TABLE [dbo].[Account]

USE MASTER
GO
DROP DATABASE MyTestDB

In above example, it will add a new column ‘Active’ with default value 1 for all the rows in table ‘[dbo].[Account]'. The above example shows that we can add new column in existing table with default value in SQL SERVER using ALTER TABLE statement.