Best way to change the datatype of Column of Table RRS feed

  • Question

  • one table contain 5 million rows then our requirement  to change the data type of any column

    what is the best way to change the datatype of this column ?

    Friday, June 14, 2013 2:25 PM


  • Are you allowed any sort of outage on this table?  If so, then consider creating a new table with the correct data type, bulk load the data into it, then drop the old table and rename the new.  I don't think 5m records is a significant enough volume to warrant any more complex a solution. 

    You can also do an ALTER TABLE/ALTER COLUMN query, as long as the data types you are converting from and to are compatible, and the data is compatible.  That's assuming the column is not part of the primary key or part of an index or constraint. 

    • Proposed as answer by Piotr Palka Monday, June 17, 2013 1:31 AM
    • Marked as answer by Mike Yin Sunday, June 23, 2013 6:28 AM
    Friday, June 14, 2013 3:05 PM