CODEWITHSUNDEEP
sqlsql-server-2008decimalcurrencysqldatatypes
Karl
Karl

Reputation: 5822

Increase precision on money field for huge table

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

Upvotes: 2

Views: 422

Answers (1)

Martin Smith
Martin Smith

Reputation: 453278

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).

Upvotes: 1

Related Questions