Adding a new column to an existing table??? Check the rules first

Hi All,
So, you are standing at the gates where you need to add a new COLUMN to an existing table.

Well, there are 2 cases:

  • Table is empty
  • Table contains data

If “Table is empty” then you are still in design-phase of that table. Add new column whichever the way you know 🙂
E.g. Using T-SQL or through design mode of table etc.

But if “Table contains data” then you must go through the following rules before you take any action, otherwise you will simply get (guiding :)) errors.

  1. ALTER TABLE only allows columns to be added that can contain nulls
  2. ALTER TABLE only allows columns to be added that have a DEFAULT definition specified
  3. The column being added is an identity or timestamp column
  4. If none of the previous conditions are satisfied the table must be empty to allow addition of new column.

Simple & interesting! Isn’t it?
Thanks, Khilit

bigatorMay 9, 2012 · bigatorKhilitchandra Prajapati · bigator3 Comments
bigatorTags: , , , ,  · Posted in: General, Interview QA, Quick Tips, SQL Server, Tutorial

3 Responses

  1. Nilesh - May 10, 2012

    Nice post Khilit.
    Thanks for sharing such simple & interesting tips.

    Keep sharing


  2. trx training outlet - May 29, 2012

    Great article, thanks for sharing.

  3. Timon Ron - June 12, 2012

    Wow, this is really good information. Thank you sir.

Leave a Reply

%d bloggers like this: