Add a column with a default value to an existing table in SQL Server.

This code example uses SQL Server 2014 Standard

In this short blog, I will show you how to add a default value to a SQL Server database table using the same table as the blog I wrote about How to Update from a Select statement in SQL Server.

To start let's create our table.

CREATE TABLE dbo.PersonNameAndAddress
( PersonId       INT
  PRIMARY KEY IDENTITY(1, 1) NOT NULL, 
  FirstName     NVARCHAR(100) NULL, 
  LastName NVARCHAR(100) NULL, 
  CityName NVARCHAR(100) NULL,
  PostCode NVARCHAR(100) NULL, 
 )
 
GO

Once we have created the table let's add some values.

INSERT INTO PersonNameAndAddress
(FirstName, LastName )
VALUES
('Elvis', 'Presley'),
('Cliff', 'Richard'),
('Michael', 'Jackson'),
('Kylie', 'Minogue'),
('Elton', 'John'),
(N'Robbie', N'Williams'),
(N'Ed', N'Sheeran'),
(N'Calvin', N'Harris'),
(N'Justin', N'Bieber'),
(N'Rod', N'Stewart')

So now we have our table and it is full of data and the boss one day comes along and say that a new column needs to be added to the existing table.

The way to achieve this is to use the alter table on the table we need to change, the code for how to do this is below.

ALTER TABLE [dbo].[PersonNameAndAddress] -- Table name
ADD Country nvarchar(100) not null -- New Column no null allowed
CONSTRAINT Country_Constraint -- Prevent SQL from createing a wierd constraint name, by adding our own
DEFAULT 'United Kingdon' -- The default value

If you now run the above code, you will see that the table now has a new column called 'Country' and the value is 'United Kingdom'

Add a column with a default value to an existing table in SQL Server.

I hope you found this short blog on how to add a column with a default value to an existing table in SQL Server helpful, thanks for reading.