October 17, 2013 at 1:53 am
Hi Folksl,
Does anyone know of the best way of doing this. The table currently has 9 million rows in it.
The only solutions I know of are:
1) alter column MyCol decimal(18,4) (will bloat log and block transactions on the table)
2) a new table and transfer the data across. (lengthily process which will require down time)
3) add a new column batch update the new column with data from the old one each night
once data is fully across, drop old column an rename new column to old name.
Thanks
October 17, 2013 at 2:05 am
It shouldn't take that long to transfer 9 million rows.
What is the recovery model? Is the table frequently queried/updated?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 17, 2013 at 2:25 am
The DB is in FULL recovery, the table gets hit at least 5-10 times a minute with writes and reads
October 17, 2013 at 3:05 am
Not sure, but this may just be a metadata change to the column. Looking at the numeric data type since your precision is not changing, the number of bytes used to store the data is not changing.
Look here: http://msdn.microsoft.com/en-us/library/ms187746(v=sql.100).aspx
October 17, 2013 at 3:09 am
Lynn Pettis (10/17/2013)
Not sure, but this may just be a metadata change to the column. Looking at the numeric data type since your precision is not changing, the number of bytes used to store the data is not changing.Look here: http://msdn.microsoft.com/en-us/library/ms187746(v=sql.100).aspx
Interesting... is it risking changing metadata? Also I have no idea how to this? 😛
I've attempted an alter column and it takes forever to execute.
October 17, 2013 at 4:19 am
bugg (10/17/2013)
Lynn Pettis (10/17/2013)
Not sure, but this may just be a metadata change to the column. Looking at the numeric data type since your precision is not changing, the number of bytes used to store the data is not changing.Look here: http://msdn.microsoft.com/en-us/library/ms187746(v=sql.100).aspx
Interesting... is it risking changing metadata? Also I have no idea how to this? 😛
I've attempted an alter column and it takes forever to execute.
Not sure what to say at this point.
October 17, 2013 at 4:29 am
Lynn Pettis (10/17/2013)
bugg (10/17/2013)
Lynn Pettis (10/17/2013)
Not sure, but this may just be a metadata change to the column. Looking at the numeric data type since your precision is not changing, the number of bytes used to store the data is not changing.Look here: http://msdn.microsoft.com/en-us/library/ms187746(v=sql.100).aspx
Interesting... is it risking changing metadata? Also I have no idea how to this? 😛
I've attempted an alter column and it takes forever to execute.
Not sure what to say at this point.
Not sure what to say either...i misread your first post but gathered that you meant the update shouldn't be that big as its just a meta data change.
Unfortunately that is not the case 🙁 ... What are your thoughts on the new column approach?
Thanks
October 17, 2013 at 5:01 am
bugg (10/17/2013)
What are your thoughts on the new column approach?
One thing i can say here is .. it will be protective approach.
create a new column with new dimension .
copy the data (update query)
Preserve the old column till you are confident that new columns have good and correect data
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 17, 2013 at 5:48 am
Bhuvnesh (10/17/2013)
bugg (10/17/2013)
What are your thoughts on the new column approach?One thing i can say here is .. it will be protective approach.
create a new column with new dimension .
copy the data (update query)
Preserve the old column till you are confident that new columns have good and correect data
I think this is the approach i'm going to take. Unless anyone think of an alternative approach?
Thanks for the help on this guys much appreciated 🙂
May 28, 2014 at 3:13 pm
bugg (10/17/2013)
Hi Folksl,Does anyone know of the best way of doing this. The table currently has 9 million rows in it.
The only solutions I know of are:
1) alter column MyCol decimal(18,4) (will bloat log and block transactions on the table)
2) a new table and transfer the data across. (lengthily process which will require down time)
3) add a new column batch update the new column with data from the old one each night
once data is fully across, drop old column an rename new column to old name.
Thanks
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply