December 19, 2014 at 7:44 am
Hi everyone,
I encountered a challenge lately at the beginning of this week that I need to alter the column data type of one column for an existing history table. And that column is an numeric column, I am expanding to precision size from 12 to 18. This table is huge. It has about 120M records, one non-unique clustered index on one column, and one default constraint on another column.
It takes forever for me to just perform an alter column statement to do the job. On the server, it took 10 minutes to alter 3M rows of record. A direct alter might not be a good idea for my case.
Therefore, I was advised to build the new table structure on the server, and load the data from the old table using SSIS package. I am only allowed to perform the migration 3 hours per day for 1 week. My question is:
1. Given the fact that I have enough drive spaces for log. Do you think this kind of 7 days incremental load is a good idea?
2. Do you think a 1 day bulk insert would be a better solution?
Looking for your valuable ideas/suggestions!
Best,
The ETL newbie
December 19, 2014 at 7:59 am
Could you add a new column to the table with the new precision, and copy the existing data into it using UPDATE table SET newCol = oldCol.
You could chunk up the updates into a few million rows at a time (giving you an opportunity to lessen the impact), and then rename the columns once complete ?
Just a thought... 🙂
[font="Courier New"]sqlmunkee[/font]
[font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]
December 20, 2014 at 1:16 am
Could you try the statement below on a development or test server using a full sized table?
The storage size for numeric precision of 12 or 18 is exactly the same (9 bytes), so it may not take as long as you think to make the change. I can't say I have every tried this particular operation on a large table, but it is worth a try if you have some place to test it.
Alter table MyTable
alter column MyColumn numeric(18,0) /*or correct precision needed*/
not null /*(or null as needed)*/
December 23, 2014 at 9:24 am
Michael Valentine Jones (12/20/2014)
Could you try the statement below on a development or test server using a full sized table?The storage size for numeric precision of 12 or 18 is exactly the same (9 bytes), so it may not take as long as you think to make the change. I can't say I have every tried this particular operation on a large table, but it is worth a try if you have some place to test it.
Alter table MyTable
alter column MyColumn numeric(18,0) /*or correct precision needed*/
not null /*(or null as needed)*/
I don't believe that will be the case if the vardecimal format is enabled for that table. The table can be tested for that like so:
SELECT OBJECTPROPERTYEX(OBJECT_ID('dbo.table_name'),
'TableHasVarDecimalStorageFormat')
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply