November 17, 2012 at 6:43 am
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
November 17, 2012 at 10:18 am
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.November 17, 2012 at 10:34 am
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?
November 17, 2012 at 11:52 am
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
November 17, 2012 at 12:03 pm
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply