locked
Production Data Type Change RRS feed

  • Question

  • Hi Experts,

    I have a very active table in the production containing over 8 billion records. I need to perform a data type from current of VARCHAR(125) to new change of VARCHAR(255). Could you please suggest the possible strategies for performing this change

    and also ways i can replicate some good amount of workload and test this change out in test environment. 

    Thanks & regards

    Priya

    Wednesday, August 30, 2017 9:02 AM

Answers

  • ALTER TABLE tbl ALTER COLUMN col varchar(255) (NOT) NULL

    As long as the column is not indexed or part of some constraint, this is a walk in the park, as this is a metadata change. You should of course test in a test environment before you go ahead. (Preferrably an envioronment which is a restored copy of production!)

    • Marked as answer by Priya Bange Wednesday, August 30, 2017 12:34 PM
    Wednesday, August 30, 2017 9:22 AM
  • Hi Priya,

    I have had to do a similar change and we tested it in test before implementing it in prod.

    I would recommend creating mock data similar in structure to your existing table and running the alter first on it. (in case you cannot get production data copied into testing)

    I use the tool available here to create mock data.

    However if the table has indexes i would recommend working with a copy. I have successfully used the methods listed here for a 100 million row table with the column being updated having an index.

    If there are triggers/ constraints you should drop them before altering.

    Hope this helps.

    Nimish

    • Marked as answer by Priya Bange Wednesday, August 30, 2017 12:34 PM
    Wednesday, August 30, 2017 10:49 AM

All replies

  • ALTER TABLE tbl ALTER COLUMN col varchar(255) (NOT) NULL

    As long as the column is not indexed or part of some constraint, this is a walk in the park, as this is a metadata change. You should of course test in a test environment before you go ahead. (Preferrably an envioronment which is a restored copy of production!)

    • Marked as answer by Priya Bange Wednesday, August 30, 2017 12:34 PM
    Wednesday, August 30, 2017 9:22 AM
  • Hi Priya,

    I have had to do a similar change and we tested it in test before implementing it in prod.

    I would recommend creating mock data similar in structure to your existing table and running the alter first on it. (in case you cannot get production data copied into testing)

    I use the tool available here to create mock data.

    However if the table has indexes i would recommend working with a copy. I have successfully used the methods listed here for a 100 million row table with the column being updated having an index.

    If there are triggers/ constraints you should drop them before altering.

    Hope this helps.

    Nimish

    • Marked as answer by Priya Bange Wednesday, August 30, 2017 12:34 PM
    Wednesday, August 30, 2017 10:49 AM
  • By logic, changing from a varchar to greater does not need to rewrite the rows.
    Saturday, September 2, 2017 5:52 AM
  • Adding on to the other answers, if the database default collation is different than the column collation, be sure to explicitly specify the existing column collation too.  Otherwise, the column collation will be changed which might not be a metadata-only operation.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, September 2, 2017 10:33 AM