Adding a new column to a table. Best approach

Question

Adding a new column to a table. Best approach

bugg

SSCertifiable

Points: 7567
More actions
November 17, 2012 at 6:43 am

#267059

The standard way to add a new column to a table is more or less the following.
Alter table <table name>
add <column> <type> NULL
In a previous company I worked in when we made a change to a table, even when adding a column to the end of the table we used to shift the data to a temporary table and drop and recreate the whole table and move the data back. Is there a good reason for doing this? Is the only reason to use this approach if the column cannot be at the end and the data can't be null or a default value?
Many Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

bugg (11/17/2012)
The standard way to add a new column to a table is more or less the following.
alter table table_name add column column_name...

That is NOT "more or less" alter table add column IS the way to do it 🙂

bugg (11/17/2012)
In a previous company I worked in when we made a change to a table, even when adding a column to the end of the table we used to shift the data to a temporary table and drop and recreate the whole table and move the data back. Is there a good reason for doing this? Is the only reason to use this approach if the column cannot be at the end and the data can't be null or a default value?

Firstly, nobody should care in which position on the table a column is. That is absolutely irrelevant - if a column has to be added, add it. Period.

Answering your question, the only reason to re-create a table just to add a column would be that somebody has decided - against all logic and reasoning - that the physical position of the new column is important.

Adding on previous comment, if a column to be added includes a default value, the way to do it is: 1) Add column as null, 2) Populate column to the "default" value thru update statement, 3) alter column specifying the default value.

Hope this helps.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

bugg SSCertifiable Points: 7567 More actions · Answer 2

PaulB-TheOneAndOnly (11/17/2012)
bugg (11/17/2012)
The standard way to add a new column to a table is more or less the following.
alter table table_name add column column_name...
That is NOT "more or less" alter table add column IS the way to do it 🙂
bugg (11/17/2012)
In a previous company I worked in when we made a change to a table, even when adding a column to the end of the table we used to shift the data to a temporary table and drop and recreate the whole table and move the data back. Is there a good reason for doing this? Is the only reason to use this approach if the column cannot be at the end and the data can't be null or a default value?
Firstly, nobody should care in which position on the table a column is. That is absolutely irrelevant - if a column has to be added, add it. Period.
Answering your question, the only reason to re-create a table just to add a column would be that somebody has decided - against all logic and reasoning - that the physical position of the new column is important.
Adding on previous comment, if a column to be added includes a default value, the way to do it is: 1) Add column as null, 2) Populate column to the "default" value thru update statement, 3) alter column specifying the default value.
Hope this helps.

Thanks SSCrazy, I think we used to do this approach because SQL compare did it when it created an update script from dev to production . Man I feel like a$$! thankyou very much.

I must ask though why does SSMS throw an error saying you should drop and recreate? Unless you uncheck the 'prevent saving changes that require a table recreate"? With the alter table add col script does that drop and recreate the table behind the scenes?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

bugg (11/17/2012)
I must ask though why does SSMS throw an error saying you should drop and recreate? Unless you uncheck the 'prevent saving changes that require a table recreate"?

Because SSMS is completely stupid and when you do table modifications via the GUI, it executes a drop and recreate of the table (create temp table, copy data across, drop old table, rename). People didn't realise that, used the GUI on a production system and locked tables for hours, blew out the tran logs, etc. MS, instead of fixing what SSMS does, added a warning option.

With the alter table add col script does that drop and recreate the table behind the scenes?

No.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bugg SSCertifiable Points: 7567 More actions · Answer 4

GilaMonster (11/17/2012)
bugg (11/17/2012)
I must ask though why does SSMS throw an error saying you should drop and recreate? Unless you uncheck the 'prevent saving changes that require a table recreate"?
Because SSMS is completely stupid and when you do table modifications via the GUI, it executes a drop and recreate of the table (create temp table, copy data across, drop old table, rename). People didn't realise that, used the GUI on a production system and locked tables for hours, blew out the tran logs, etc. MS, instead of fixing what SSMS does, added a warning option.
With the alter table add col script does that drop and recreate the table behind the scenes?
No.

Thanks Gila, that clears up all my misconceptions.