SQL SERVER – Add New Column With Default Value

SQL Server is a very interesting system, but the people who work in SQL Server are even more remarkable. The amount of communication, the thought process, the brainstorming that they do are always phenomenal. Today I will share a quick conversation I have observed in one of the organizations that I recently visited.

While we were heading to the conference room, we passed by some developers and I noticed the following script on the screen of one of the developers.

CREATE TABLE TestTable
(FirstCol INT NOT NULL)
GO
------------------------------
-- Option 1
------------------------------
-- Adding New Column
ALTER TABLE TestTable
ADD SecondCol INT
GO
-- Updating it with Default
UPDATE TestTable
SET SecondCol = 0
GO
-- Alter
ALTER TABLE TestTable
ALTER COLUMN SecondCol INT NOT NULL
GO


Curious, I asked why he wrote such a long script. He replied with another question, asking whether there is any existing short method that he can use to add a column which is not null and can be populated with some specific values.

Of course! The method exists and here is the quick script. When he learned it he said, “Well, I searched at your blog but it was not there.”

------------------------------
-- Option 2
------------------------------
-- Adding Value with Default Value
ALTER TABLE TestTable
ADD ThirdCol INT NOT NULL DEFAULT(0)
GO

Well, now it’s in my blog.