I have a question: What will happen internally if i add columns with NULL values to a table which are already existing (exactly with the same attributes)? Is there any activity taking place, such as row distribution etc.?
I ask because of the following reason: I have very bad performance in a stored procedure which is adding columns to a populated tables. I have runtimes up to one hour, I assume because the rows are up to 50-60KB in size and a lot of data block manipulation happens.
My plan is to add the columns in a preceding step, on the empty table (populating them with NULL values), but keep the "ALTER TABLE ADD column" step in the existing code (as it's very sensitive to a change; it's doing the production surrogate key process and i would not like to touch it without intense testing).
Do you think this will work? My hope is that no activity takes place if the columns exist already, and the performance issue is gone. Can somebody help me with this?
I often add existing columns to populated tables in temp databases in order to update the lastaltertimestamp. This prevents the table from being dropped when the cleanup job runs.
Even on large tables it's very fast and I see no CPU use, only small I/O
I expect you will get an error saying that you cannot add another column with the same name if you try to ALTER and add a column which already exists.
The ALTER TABLE works, f you don't alter the column attributes and no statistics are defined on the column(s).
Interesting. You will have to test then to see if it recognizes that there is no work to do and optimizes it to a no-action ALTER.