SQL: Add a column with a default value in existing table

Few minutes back I was working with a table in my DB and I wanted to add a new column with a default value to my existing table. I decided to write a quick blog on the script I used for the same.

Syntax:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}

Example:

Let's create an example to do the same. We will first create a table and will insert some dummy data to it:

CREATE TABLE TestTable
    (
      [ID] INT IDENTITY ,
      [Name] NVARCHAR(20)
    )
    GO
INSERT  INTO TestTable
        SELECT  'John'
        UNION ALL
        SELECT  'Peter' 
GO       

Now let's add a new column RankId with default value 0 to it.

ALTER TABLE TestTable
ADD RankId INT NOT NULL
CONSTRAINT DF_TestTable_RankId DEFAULT 0 

Now to test whether it will work or not we are going to add a row to our existing data without specifying that column in our insert query.

INSERT INTO TestTable
        ( Name )
VALUES  ( 'Dave')
GO

SELECT * FROM TestTable

--Output
--ID          Name                 RankId
------------- -------------------- -----------
--1           John                 0
--2           Peter                0
--3           Dave                 0

As you can see we have default value 0 for our newly inserted column.