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

  • 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

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

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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

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

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • ALTER TABLE dbo.tablename

    ALTER COLUMN column_name decimal(19, 6)

    And how to check the table re-creation?

  • 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