Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Alter numeric(12,6) to numeric(19,4) - is it metadata only or is it recreate table? Expand / Collapse
Author
Message
Posted Monday, March 18, 2013 7:23 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 5:44 PM
Points: 368, Visits: 714
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
Post #1432187
Posted Monday, March 18, 2013 8:15 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:53 PM
Points: 22,511, Visits: 30,236
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1432200
Posted Monday, March 18, 2013 10:28 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, April 18, 2014 4:53 PM
Points: 1,746, Visits: 2,553
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)
I'm not fat, I'm gravity challenged.
Post #1432259
Posted Monday, March 18, 2013 8:02 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 5:44 PM
Points: 368, Visits: 714
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
Post #1432435
Posted Tuesday, March 19, 2013 1:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, February 23, 2014 7:21 AM
Points: 56, Visits: 493
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.
Post #1432512
Posted Tuesday, March 19, 2013 8:59 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, April 18, 2014 4:53 PM
Points: 1,746, Visits: 2,553
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)
I'm not fat, I'm gravity challenged.
Post #1432711
Posted Wednesday, March 20, 2013 1:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, February 23, 2014 7:21 AM
Points: 56, Visits: 493

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


And how to check the table re-creation?
Post #1433042
Posted Wednesday, March 20, 2013 3:38 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:05 AM
Points: 21, Visits: 493
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"
Post #1433091
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse