Alter numeric(12,6) to numeric(19,4) - is it metadata only or is it recreate table?

Question

Alter numeric(12,6) to numeric(19,4) - is it metadata only or is it recreate table?

Ol'SureHand

SSCrazy

Points: 2744
More actions
March 18, 2013 at 7:23 am

#290994

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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.

ScottPletcher SSC Guru Points: 101123 More actions · Answer 2

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

Ol'SureHand SSCrazy Points: 2744 More actions · Answer 3

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

salliven SSCrazy Points: 2644 More actions · Answer 4

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.

ScottPletcher SSC Guru Points: 101123 More actions · Answer 5

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

salliven SSCrazy Points: 2644 More actions · Answer 6

ALTER TABLE dbo.tablename
ALTER COLUMN column_name decimal(19, 6)

And how to check the table re-creation?

marc.snoeys Ten Centuries Points: 1106 More actions · Answer 7

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"