SQL Server Performance

Add New column in existing Table

Discussion in 'Getting Started' started by jagblue, Sep 20, 2007.

  1. jagblue New Member

    I have a table with 20 columns in it with 70 million records I want to add 2 new column's in between column 8 and 9 not in the endI can do it with GUI but I want to do this using script. If I used script generated from GUI it drops all indexes and constrains and recreates it which takes almost 3 hrs.Is their simple way to add these new columns Instead of using script generated by GUI?
    Thank You.
  2. thomas New Member

    No, there is no quick way, even in T-SQL, to add them in a specific place in the column order. you have to create a new table with the correct order, and copy all the table from the existing one to the new one. This gets very complicated if you have lots of foreign keys and indexes.

    column order does not matter in a table though, you can select the columns in any order you want. Why does it matter that they go in the place you mention?
  3. jagblue New Member

    Thank‘s Thomas
    Our business rule is to keep audit column in the end.So it makes sense to put these new column in middle with proper grouping like all column related to values keep together.
  4. ghemant Moderator

    You can perform the activity as said by Thomas, but this will take a time, if this is necesaary schedule this in off-pick hour to avoid any hassels.
  5. DilliGrg Member

    What if your requirement changes next time again? Let's say you need to add one more column in the middle, do you want to redo it all over again just because of the audit? This approach will only be suitable if this table never changes again otherwise it doesn't make sense doing the same lengthy process over and over again. Order of the column doesn't really matter within the table since you can select the order the way you want in your query.

Share This Page