I am working for a company that has a 3rd party vendor application.
The Table that having an ALTER Column run against it is a 300 column heap table (import only).
The vendors application issues a number of ALTER TABLE ALTER COLUMN (sometimes 50-60 separate columns) for each import, The change is the data type length eg Decimal (18,2) to (30,10).
These seem to be taking a long time to execute especially when the table has around 4GB of data in it.
Is it possible that the problem is being caused because the data requires extra pages so the engine is spooling the existing table into the TempDB, and back again once the space has been allocated or is it a meta data only operation?
Not really answering your question, but surely altering the table with data in it is liable to failure, if any columns ever shrink?
If this is an 'import table', I would have expected it to be empty immediately preceding each import ...
But I guess the vendor has reasons.
If the columns are only ever getting bigger, why not make them all huge right now and do away with the ALTER issue?
Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this
If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.