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.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

29 thoughts on “SQL SERVER – Add New Column With Default Value

  1. Although , I do not understand SQL Server much but the way you explained in this particular post is awesome…

    Other bloggers (Including me ) might have just blogged the last script saying “This script is for this purpose ” but the way you cam to the point is really awesome …

    great articulated

    Thanks sir

    Dhananjay Kumar

  2. A reason you may want to create the column without a default is: if it’s a large table, it will take exponentially longer and will result in locking/blocking. Creating a new column without a default takes no time regardless the size of the table. Then you can populate the table in increments, then add the NOT NULL as a last step.

  3. Yes but altering an existing table for adding an new column with default value,the update statement is necessary for updating the existing data in table with default value.

  4. Hi Dave,

    use AdventureWorks
    select
    FirstName +’ ‘+ LastName as ‘Name’
    from Person.Contact
    go

    this will give you the output like

    Name
    ——–
    Gustavo Achong
    Catherine Abel
    Kim Abercrombie
    Humberto Acevedo
    Pilar Ackerman

    but i wnat the output like

    temp_id Name
    ———– ———–
    1 Gustavo Achong
    2 Catherine Abel
    3 Kim Abercrombie
    4 Humberto Acevedo
    5 Pilar Ackerman

    so i want it only in one select statement.

    i tried like

    use AdventureWorks
    select
    IDENTITY(1,1) as ‘temp_id’, FirstName +’ ‘+ LastName as ‘Name’
    from Person.Contact
    go

    do i have any option for that in sqlserver.

  5. Great one. But 1 small confusion (when it comes to memorize the syntax of ALTER Statement):

    For the ALTER statement, when we ADD a new column there is no keyword “COLUMN”, but when we ALTER the column then it uses the keyword “COLUMN”.

    Wish there was consistency, so that we could fire the SQL without any compilation error for the 1st time ;)

  6. I’m using “method 2″ via SQL-Server Management Studio. Very useful for adding additional fields. I find it easier to remember via UI rather written query.

    Pinal, as probably the most dedicated SQL teacher online what the reason behind tables with NULL values at all except the fact they take less HDD space than 0 for instance? It is much harder to maintain those in ASP.NET later on.

    • Hi Eugene,

      Great point. The reason is NULL is not optimal when it is about performance. They also do not give proper estimation of the statistics which helps Query Optimizer to give proper plans for query.

  7. Nice explanation on the use of NULL and its effect on performance. I have seen places where business rules are built around when a certain column is NULL, I feel that is not a good practice. Good script.

  8. I want to add column description in alter statement. How to mention Column description in the alter statement.

    example.

    Columnname Desc.

    AltModelSlNo. AlternativeModelSerialNo.

    how to specify.in the alter statement.

  9. hi,
    i create table “emp” and insert data into it.
    Then,i add column in table “emp” contain default constraint.
    Now, i want to set the value of default in pre-existing table”emp” which contain data.
    how can i set default value???

    p.s:i need answer its urgent.

  10. For adding a default to a table with pre-existing data you can do it all at one with ALTER TABLE xxx ALTER COLUMN yyyy DEFAULT (0) WITH VALUES

    The WITH VALUES will apply the default value to the NULL fields

  11. Hello,

    Ok, so i have used the syntax:
    ALTER TABLE TestTable
    ADD ThirdCol INT NOT NULL DEFAULT(0)

    but when i use this syntax:
    ALTER TABLE TestTable
    ADD ThirdCol INT NULL DEFAULT(0)

    the default 0 is not applied, the field still holds a null value. Can you please tell me why so?

  12. Pingback: SQL SERVER – Weekly Series – Memory Lane – #024 | SQL Server Journey with SQL Authority

  13. Hi sir i want to drop the default value but it is not successful please help me
    here i mentioned my codings

    create table colour(type1 varchar(50), type2 varchar(50), type3 varchar(50) default ‘blue’)
    insert into colour values(‘white’, ‘black’,’orange’)
    select * from colour
    insert into colour values(‘block’,’gray’,’ ‘)
    insert into colour values(‘rose’,’green’,default)
    insert into colour (type1,type2) values (‘gg’,’bb’)

    alter table colour alter column type3 drop default

    error:Incorrect syntax near the keyword ‘default’.

  14. My case is I added a exsisting column With a default Unique Value i had 20000 records almost
    using a Row number

    I had added a AdmissionForm_No Column with Datatype Varchar

    Run this
    with xyz as (select AdmissionForm_No, Row_number() over(order by Reg_no asc) as RN from Students)
    Update xyz Set AdmissionForm_No = RN

    and AdmissionForm_No was updated by unique Row Number Value

    it did my Half Work but issue is
    i need to append ‘Frm-’ now Against each record of AdmissionForm_No
    Like
    Frm-00001,Frm-2, frm-3, frm-30051,frm-55555

  15. Not sure if I am missing something, but NULL is used when a value is not known. This applies to both numeric and char based columns. For example an age from four rows where all four ages are known or only three are know will be different. say the total was 100 from the four, the average would be 25. If only three were known i.e. one was NULL the average would be 33.3r. You cannot substitute 0 for null in this case

  16. The results of Option 1 and 2 are not the same, and one should be used over the other depending on the purpose.

    It might make sense to update all the current records “SecondCol” to 0, but what if you want to force this value to be set explicitly for future entries. By using “default” this will implicitly set the value to 0 for INSERT statements which don’t reference SecondCol.

    This is fine in most cases, but thought I should just point out “Option 2″ is not a short-hand method of “Option 1″. They both produce different schemas.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s