January 24, 2011 at 11:44 pm
Hi All,
I have a table in one of my Db's of around 130GB and it has many columns, 1 of my user requested to change the column lentgh from numeric(15,2) to numeric(21,10) and when i am changing it through GUI by right clicking the table and modify, it is giving me warning that by doing this i might loose all the data.
how can i do this without loosing the data? Please help
NEVER NEVER EVER GIVE UP;-)
January 25, 2011 at 12:01 am
alter table tablename
alter column columnname numeric(21,0)
execute this against the database which has that table whose column data type needs to be changed.
Satnam
January 25, 2011 at 12:02 am
Sorry its numeric(21,10) and not numeric(21,0)
Satnam
January 25, 2011 at 12:31 am
scorpianprince (1/24/2011)
Hi All,I have a table in one of my Db's of around 130GB and it has many columns, 1 of my user requested to change the column lentgh from numeric(15,2) to numeric(21,10) and when i am changing it through GUI by right clicking the table and modify, it is giving me warning that by doing this i might loose all the data.
how can i do this without loosing the data? Please help
Maybe your getting that error because 15,2 > 21,10. In the first case you have 13 digits and 2 decimals. In the second 11 digits and 10 decimals. So say you have the value 1234567890123.45 stored. This cant be stored in 21,10. Now if you try alter table it will warn you about that... simply it wont let you alter the table (Arithmetic overflow). Maybe the GUI tries to get around the problem somehow and the "dataloss" is connected to that.
/T
January 25, 2011 at 4:40 am
Hi Satnam
alter table tablename
alter column columnname numeric(21,10)
by this command my data won't be lost as you know its a 130GB table and if anything happens to this table then i am in trouble.
NEVER NEVER EVER GIVE UP;-)
January 25, 2011 at 10:07 am
Do NOT go from 15,2 to 21, 10. You will most likely lose data.
Why?
15,2: 1234567890123.45
21,10: 12345678901.234567901
Note where the decimal ends up in precision.
You want 23,10, if you're insisting on 10 decimal places, or 21,8, if they insist on the 21 characters.
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
January 25, 2011 at 10:24 am
Four points..
1. You are losing decimals to the left of the point, I would only do this IF I had verified MYSELF that there is NO data that will be affected, if so much as a row is affected the change is off.
2. Do not do this from the GUI it will likely timeout and rollback, use the alter statements already put forth, its safer.
3. I would question the change, it *feels* arbitrary (10 decimals, really?), and while I hate basing anything on feelings, is the new datatype correct, you don't want to change it again. Very often type changes are due to insufficient research and planning up-front, not always, but often. For very large tables it is particularly important to get it right the first time since changes can be very painful.
4. Your log file is going to get BIG, I hope you have taken into account this.
CEWII
January 25, 2011 at 12:04 pm
Much good advice given.
Also, Do you have good backups of database & t-log ?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply