March 18, 2013 at 7:23 am
Would be most grateful for confirmation/infirmation of theory below:
Changing an existing table column from NUMERIC (12,6) to NUMERIC (19,4) should be a metadata change only (in-place - no need to dump the table to tempdb and recreate) as the column size remains the same - 9 bytes.
It will require "examination" of all rows but not a recreate.
This is extremely important as I intend to alter a table with 1 billion rows.
TIA
March 18, 2013 at 8:15 am
No, you are changing the precision of the values and could lose data as you are going from 6 decimals places to 4 decimal places.
March 18, 2013 at 10:28 am
Could you go to NUMERIC(19,6)? That should work fine, and I think it will be just a metadata change.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 18, 2013 at 8:02 pm
Thanks, Lynne & Scott,
this is why this forum is so helpful.
Of course, it can work out with NUMERIC(19,6) - I just didn't think the scale was important in trying to keep the table from being dumped and rebuilt.
Much appreciated.
So can we take it as a fact that keeping the same scale as before (from 12,6 to 19,6) but increasing the precision (still within the same storage size of 9 bytes) will be just a metadata change and will not cause the 1 billion rows to dump into tempdb ??
Many thanks
March 19, 2013 at 1:51 am
Hi!
1.) Management Studio => Tools => Options => Designers => "Click the Prevent saving changes..." (Checkbox is on)
2.) Search your table on the management studio => Right click => Design
3.) Change column definition (without save)
4.) Table Designer menu => Generate Change Script. If you get error, then process is use table re-creation. If you get the code, then your change is not use table re-create.
March 19, 2013 at 8:59 am
You don't really need SSMS to generate the statement. [And of course you don't try to make the change using SSMS, as SSMS will rebuild the entire table foe even the simplest change.]
Just use the standard ALTER statement, and you should not have any problems:
ALTER TABLE dbo.tablename
ALTER COLUMN column_name decimal(19, 6)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 20, 2013 at 1:52 am
ALTER TABLE dbo.tablename
ALTER COLUMN column_name decimal(19, 6)
And how to check the table re-creation?
March 20, 2013 at 3:38 am
When using ALTER-statements the table will not be re-created.
You will however receive an error when the ALTER-statement will not work.
For instance: if the data already in that column won't fit the new precision.
"Walking on water and developing software from a specification are easy if both are frozen." -- Edward V. Berard, "Life-Cycle Approaches"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy