none
General Scenario- Adding columns into a table with more than 100 million rows RRS feed

  • Question

  • I was asked/given a scenario, what issues do you encounter when you try to add new columns to a table with more than 200 million rows? How do you overcome those?

    Thanks in advance.


    svk

    Tuesday, April 15, 2014 10:16 PM

Answers

  • Simply adding a column using TSQL will not take much time. i.e
    ALTER TABLE tableName ADD columnName VARCHAR(10) NULL

    However if we are inserting a new column in between two existing column then it may take some time as the database engine needs to recreate a new table and move the data to the new table. It is not recomended to do such things in production when the size of the table is large. Copy the table, add the require column and then finally swap it or rename it to the production.


    Regards, RSingh

    • Marked as answer by czarvk Wednesday, April 16, 2014 2:05 PM
    Wednesday, April 16, 2014 5:01 AM
  • For such a large table, it is better to add the new column to the end of the table to avoid any performance impact, as RSingh suggested.

    Also avoid to use any default on the newly created statement, or SQL Server will have to fill up 200 million fields with this default value. If you need one, add an empty column and update the column by using small batches (otherwise you lock up the whole table). Add the default after all the rows have a value for the new column.



    • Marked as answer by czarvk Wednesday, April 16, 2014 2:05 PM
    Wednesday, April 16, 2014 6:58 AM

All replies

  • Simply adding a column using TSQL will not take much time. i.e
    ALTER TABLE tableName ADD columnName VARCHAR(10) NULL

    However if we are inserting a new column in between two existing column then it may take some time as the database engine needs to recreate a new table and move the data to the new table. It is not recomended to do such things in production when the size of the table is large. Copy the table, add the require column and then finally swap it or rename it to the production.


    Regards, RSingh

    • Marked as answer by czarvk Wednesday, April 16, 2014 2:05 PM
    Wednesday, April 16, 2014 5:01 AM
  • For such a large table, it is better to add the new column to the end of the table to avoid any performance impact, as RSingh suggested.

    Also avoid to use any default on the newly created statement, or SQL Server will have to fill up 200 million fields with this default value. If you need one, add an empty column and update the column by using small batches (otherwise you lock up the whole table). Add the default after all the rows have a value for the new column.



    • Marked as answer by czarvk Wednesday, April 16, 2014 2:05 PM
    Wednesday, April 16, 2014 6:58 AM