none
Alter a table with 800 million rows RRS feed

  • Question

  • Hello,
    I would like to alter a table with 800 million rows.
    What is the fastest way to do?
    Thank you in advance
    Friday, November 23, 2012 4:48 PM

Answers

  • If you add a nullable colum, then it's done with a few seconds/minutes, because only less additional space must be aquired.

    Only when you want to add a new not nullable column, then you have to define a default value for it and then all 800 mio data rows will be updated; that will take a long time.

    So add it first as nullable, start to manully update batch by batch the null value to a predefine value (lets say, 10tsd rows per batch). After all rows are updated you can change the defintion to "not nullable".

    Of course you should test it on a test system before.


    Olaf Helper

    Blog Xing


    • Edited by Olaf HelperMVP Friday, November 23, 2012 5:33 PM
    • Proposed as answer by Naomi NModerator Friday, November 23, 2012 6:50 PM
    • Marked as answer by deppsos Monday, November 26, 2012 8:55 AM
    Friday, November 23, 2012 5:31 PM

All replies

  • That depends on what you want to alter?

    If you want to add a nullable column, then it's not a big deal.

    Otherwise ..... it depends on ...


    Olaf Helper

    Blog Xing

    Friday, November 23, 2012 5:05 PM
  • this is NOT NULL column but I do not worry I can make it null
    Friday, November 23, 2012 5:16 PM
  • Hi,

    The syntax looks like this

    ALTER TABLE <TableName> ALTER column <ColumnName> <New date type>

    Best practices for SQL Server database ALTER table operations

    http://www.mssqltips.com/sqlservertip/1903/best-practices-for-sql-server-database-alter-table-operations/


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/


    Friday, November 23, 2012 5:18 PM
  • it will take 2 days NO ?
    Friday, November 23, 2012 5:20 PM
  • If you add a nullable colum, then it's done with a few seconds/minutes, because only less additional space must be aquired.

    Only when you want to add a new not nullable column, then you have to define a default value for it and then all 800 mio data rows will be updated; that will take a long time.

    So add it first as nullable, start to manully update batch by batch the null value to a predefine value (lets say, 10tsd rows per batch). After all rows are updated you can change the defintion to "not nullable".

    Of course you should test it on a test system before.


    Olaf Helper

    Blog Xing


    • Edited by Olaf HelperMVP Friday, November 23, 2012 5:33 PM
    • Proposed as answer by Naomi NModerator Friday, November 23, 2012 6:50 PM
    • Marked as answer by deppsos Monday, November 26, 2012 8:55 AM
    Friday, November 23, 2012 5:31 PM