none
Best way for altering datatype of a column in a 500 Million record table RRS feed

  • Question

  • Hi,

    I'm looking for a best way for altering datatype of a column which is integer right now to varchar(32). The table has 500 Million records currently. and Alter table command is taking lot of time. I tried creating another column with new datatype and updating it's value to old column by update statement. But both of these operations are consuming lot's of space in transaction log and taking time.

    Is there any other I shuold try oing this?

    Thanks for any help in advance,

    Amey

    Thursday, June 17, 2010 11:49 PM

Answers

  • You could create another table, import the data and rename after dropping the existing one but you have to ensure integrity is kept (constraints etc...)  That method may not be any quicker and I would only run it as a test in your development or other test database servers to ensure (again) the integrity of the table remain stable.  It also will log everything in Full Recovery just as much as the ALTER

    Altering a table that size will take some time generally.  Dropping indexes and recreating them after is another step you can take to speed it up.  If you also cause severe page level changes in the expansion, it will take time. 

    Depending on your recovery plan, DR and HA situation, you can alter the database to set it into simple recovery.  I don't really recommend this but it is a way to minimally log to some extent.  My reasoning for not recommending it is, changing recovery of a database is a major change (IMO) and any change like that leaves you vulnerable. 


    Ted Krueger Blog on lessthandot.com @onpnt on twitter
    • Marked as answer by amey c Monday, June 21, 2010 6:22 PM
    Friday, June 18, 2010 12:32 AM
    Moderator
  • There are different ways to do this. They all have their own advantages and disadvantages.

    I think the method you describe (using ALTER TABLE) will take the least amount of time, and also the least amount of log space.

    If your problem is not necessarily the time it takes, but rather that the table is unavailable for any other use, then you could add a new column, copy the int values to the new column in batches (UPDATE), then rename the new and old column (swap) and finally drop the old column. If you keep your batches small, then blocking should be minimal.

    -- 

    Gert-Jan

    • Marked as answer by amey c Monday, June 21, 2010 6:23 PM
    Saturday, June 19, 2010 4:57 PM

All replies

  • You could create another table, import the data and rename after dropping the existing one but you have to ensure integrity is kept (constraints etc...)  That method may not be any quicker and I would only run it as a test in your development or other test database servers to ensure (again) the integrity of the table remain stable.  It also will log everything in Full Recovery just as much as the ALTER

    Altering a table that size will take some time generally.  Dropping indexes and recreating them after is another step you can take to speed it up.  If you also cause severe page level changes in the expansion, it will take time. 

    Depending on your recovery plan, DR and HA situation, you can alter the database to set it into simple recovery.  I don't really recommend this but it is a way to minimally log to some extent.  My reasoning for not recommending it is, changing recovery of a database is a major change (IMO) and any change like that leaves you vulnerable. 


    Ted Krueger Blog on lessthandot.com @onpnt on twitter
    • Marked as answer by amey c Monday, June 21, 2010 6:22 PM
    Friday, June 18, 2010 12:32 AM
    Moderator
  • There are different ways to do this. They all have their own advantages and disadvantages.

    I think the method you describe (using ALTER TABLE) will take the least amount of time, and also the least amount of log space.

    If your problem is not necessarily the time it takes, but rather that the table is unavailable for any other use, then you could add a new column, copy the int values to the new column in batches (UPDATE), then rename the new and old column (swap) and finally drop the old column. If you keep your batches small, then blocking should be minimal.

    -- 

    Gert-Jan

    • Marked as answer by amey c Monday, June 21, 2010 6:23 PM
    Saturday, June 19, 2010 4:57 PM