June 25, 2014 at 1:47 pm
Hello, everyone.
I have a few columns in different tables that were created as decimal(18,0).
I want to change them to decimal (18,4), decimal(15,4), and decimal(9,2).
These changes are on large transaction tables (currently 300 million+ records) and they are included in an index on the table.
What kind of performance impact should I expect here? Is it enough to warrant instead creating a new column and moving the values to it in batches then dropping the old column?
Also, assume that the current values in the table are not larger than the new data types.
Thanks in advance for your assistance!
June 25, 2014 at 2:39 pm
Do you have any downtime for this database/table?
I would expect a pretty heavy hit for this. Heavy enough that I'd recommend you do a table swap if you can. You'll have to account for new rows after the main insert and updates, etc, if you can't get the DB to lock down (from writes, at least) for a little while.
You can allow read access to continue during this process though, if you want. Basically create a table, make your changes, and INSERT INTO SELECT FROM the old table. Then do an sp_rename on the tables.
Something with that many rows though I'd fight for a downtime period if at all possible, even if it's 1 in the morning. Just too much to try to track for to do a one off.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 26, 2014 at 9:46 am
Any downtime incurred would have to be in seconds. We can't really afford for it to be down for any longer. I may put a trigger on the first table to insert new records into a second table with the correct data type while the records are being copied into the second table.
Would the decimal(18,0) to decimal(18,4) run faster since the precision isn't changing? I'm wondering if perhaps this would just be a metadata change or if it will act the same way as any other data type change.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply