Jason-299789 (5/29/2014)
Thanks for the reply Phil. I would agree about setting them to a max size and leaving it, but we have no control over the database. It seems the 3rd Party application had a number of templates for import from Excel/CSV and these vary depending on the supplier of the original template, thus the changes to columns.I personally wouldn't develop that or after load process the data, then truncate the table and start on the next import format.
From what I've read the DECIMAL data type is a Fixed length, which makes sense, so there is likely to be a lot of disk IO when making a change to a column to increase as data after the column has to be shuffled, while on the decrease side, a scan is performed to ensure that the data will fit into the new scale and precision.
It doesn't help that 40+ columns can change and they are each being run as an ATLER TABLE ALTER COLUMN.
It seems that you have no control whatsoever over the schema of this beast. So maybe instead you can do something about the data which is in it at the time that the ALTERs get executed?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.