Post reply

Need to change column type from decimal(18,2) to decimal(18,4) on fairly big table.

  • October 17, 2013 at 1:53 am

    #278979

    Hi Folksl,

    Does anyone know of the best way of doing this. The table currently has 9 million rows in it.

    The only solutions I know of are:

    1) alter column MyCol decimal(18,4) (will bloat log and block transactions on the table)

    2) a new table and transfer the data across. (lengthily process which will require down time)

    3) add a new column batch update the new column with data from the old one each night

    once data is fully across, drop old column an rename new column to old name.

    Thanks

  • October 17, 2013 at 2:05 am

    #1659157

    It shouldn't take that long to transfer 9 million rows.

    What is the recovery model? Is the table frequently queried/updated?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • October 17, 2013 at 2:25 am

    #1659163

    The DB is in FULL recovery, the table gets hit at least 5-10 times a minute with writes and reads

  • October 17, 2013 at 3:05 am

    #1659172

    Not sure, but this may just be a metadata change to the column. Looking at the numeric data type since your precision is not changing, the number of bytes used to store the data is not changing.

    Look here: http://msdn.microsoft.com/en-us/library/ms187746(v=sql.100).aspx

  • October 17, 2013 at 3:09 am

    #1659177

    Lynn Pettis (10/17/2013)

    Not sure, but this may just be a metadata change to the column. Looking at the numeric data type since your precision is not changing, the number of bytes used to store the data is not changing.

    Look here: http://msdn.microsoft.com/en-us/library/ms187746(v=sql.100).aspx

    Interesting... is it risking changing metadata? Also I have no idea how to this? 😛

    I've attempted an alter column and it takes forever to execute.

  • October 17, 2013 at 4:19 am

    #1659204

    bugg (10/17/2013)

    Lynn Pettis (10/17/2013)

    Not sure, but this may just be a metadata change to the column. Looking at the numeric data type since your precision is not changing, the number of bytes used to store the data is not changing.

    Look here: http://msdn.microsoft.com/en-us/library/ms187746(v=sql.100).aspx

    Interesting... is it risking changing metadata? Also I have no idea how to this? 😛

    I've attempted an alter column and it takes forever to execute.

    Not sure what to say at this point.

  • October 17, 2013 at 4:29 am

    #1659206

    Lynn Pettis (10/17/2013)

    bugg (10/17/2013)

    Lynn Pettis (10/17/2013)

    Not sure, but this may just be a metadata change to the column. Looking at the numeric data type since your precision is not changing, the number of bytes used to store the data is not changing.

    Look here: http://msdn.microsoft.com/en-us/library/ms187746(v=sql.100).aspx

    Interesting... is it risking changing metadata? Also I have no idea how to this? 😛

    I've attempted an alter column and it takes forever to execute.

    Not sure what to say at this point.

    Not sure what to say either...i misread your first post but gathered that you meant the update shouldn't be that big as its just a meta data change.

    Unfortunately that is not the case 🙁 ... What are your thoughts on the new column approach?

    Thanks

  • October 17, 2013 at 5:01 am

    #1659223

    bugg (10/17/2013)

    What are your thoughts on the new column approach?

    One thing i can say here is .. it will be protective approach.

    create a new column with new dimension .

    copy the data (update query)

    Preserve the old column till you are confident that new columns have good and correect data

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • October 17, 2013 at 5:48 am

    #1659254

    Bhuvnesh (10/17/2013)

    bugg (10/17/2013)

    What are your thoughts on the new column approach?

    One thing i can say here is .. it will be protective approach.

    create a new column with new dimension .

    copy the data (update query)

    Preserve the old column till you are confident that new columns have good and correect data

    I think this is the approach i'm going to take. Unless anyone think of an alternative approach?

    Thanks for the help on this guys much appreciated 🙂

  • May 28, 2014 at 3:13 pm

    #1717183

    bugg (10/17/2013)

    Hi Folksl,

    Does anyone know of the best way of doing this. The table currently has 9 million rows in it.

    The only solutions I know of are:

    1) alter column MyCol decimal(18,4) (will bloat log and block transactions on the table)

    2) a new table and transfer the data across. (lengthily process which will require down time)

    3) add a new column batch update the new column with data from the old one each night

    once data is fully across, drop old column an rename new column to old name.

    Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply