2

I have a huge data table (200+ million records) which stores cash amounts for which we are using the Money datatype. I need to increase the precision of this field to 8 or so decimal points.

Now I could simply go 

ALTER TABLE CashTable ALTER COLUMN Cash decimal(23,8) null

I would like to know though if anyone has an idea of how this will perform, and whether there is any risk of loss of data.

Thanks

Karl
  • 5,573
  • 8
  • 50
  • 73
  • Do you need to do this online or do you have a maintenance window you can do this in? What is the recovery model of the database? – Martin Smith Feb 08 '12 at 12:23

1 Answers1

1

Behind the scenes this will add a new fixed length column to each row so if you need to do this as an online operation in order to keep the locking overhead down and to reduce the transaction size if I were you I would simply do this explicitly.

ALTER TABLE CashTable ADD COLUMN Cash2 decimal(23,8) null

Then add an INSERT and UPDATE trigger to keep Cash2 synched with the Cash column and update the new column in batches.

When the whole table is synched drop the original column and rename the new one. Hopefully you don't have any code dependant upon column ordinal order.

There is no risk of data loss as this can cope with the range of money fine (-922,337,203,685,477.5808 to 922,337,203,685,477.5807).

Martin Smith
  • 438,706
  • 87
  • 741
  • 845