March 27, 2008 at 9:54 am
Twice a year, we migrate changes from a development/test environment to a production environment. One of the developers insists that the proper way to script a simple change - e.g. adding a column to an existing table - is as follows (in pseudo-code):
1. create a temp table with same structure as the existing table
2. copy all records from existing table to temp table via an INSERT statement
3. drop the existing table
4. create a new table with the old structure plus the new column
5. add the primary key constraint to the new table
6. copy the data from the temp table to the newly created table again with an INSERT
7. add other indexes to the newly created table
8. drop the temp table
I maintain that the proper way to script this change is to use a single ALTER TABLE statement to Add the new column into the existing table.
Note also that the other developer does not use any transactions or error checking in his multi-step script so it can fail at any stage requiring a manual recovery. The multi-step script will also take longer: it's moving the entire contents of the table twice and re-indexing everything at steps 6 and 7. Ironically, the developer that writes the script this way complains that this migration takes such a long time.
With the ALTER TABLE method, the change is protected by an implicit transaction (as I understand it, single statements are ACID compliant), the entire table contents are not being copied twice and, unless the new column itself is indexed, no indexing occurs when adding the new column.
I'm looking for insights/facts to help me convince the project manager that the multi-step script is inefficient and error-prone. Alternatively, if the other developer is correct, I'm looking for an explanation of why the seemingly obvious shortcomings of the multi-step process are better than the ALTER TABLE method.
Thanks,
Chris Judge
March 27, 2008 at 10:09 am
Chris,
The reason the developer prefers this method is Microsoft creates its change script just like this. If you make a change to a table via SSMS, a create change script button will become active. The change script is generated using the same methodology you posted. The generated script does not include error checking or rollback capabilities. This is probably the script he/she is sending you.
In my opinion this is simply overkill for adding a column. Why destroy a table to add a column? With that said, this method can be very beneficial in some situations where columns are being dropped and/or datatype are being changed. I always find that after I generate a change script, I go back and modify the script. I add error logging, check for the existence of objects before creating/modifying/deleting etc.
Anyway, I never use this method for adding columns but do use it regularly for most other situations. I believe this is a matter of convenience, not best practice.
March 27, 2008 at 10:19 am
Adam, i think the whole drop thing and rename etc is only scripted by SSMS when you alter a column (create new table with new structure, copy accross the data, delete orig table and rename "new" table). I think for adding a new column it simply scripts the alter table add XXXX etc.
I havnt actually checked this, so i await to be shot down 😛
March 27, 2008 at 10:24 am
ok, ill shoot myself down. I tried it and Adam is right 😛
Thats stupid imo 🙂
March 27, 2008 at 10:28 am
Adam, i think the whole drop thing and rename etc is only scripted by SSMS when you alter a column (create new table with new structure, copy accross the data, delete orig table and rename "new" table). I think for adding a new column it simply scripts the alter table add XXXX etc.
I havent actually checked this, so i await to be shot down
I believe you are right, but I have not checked this in a while either :D. However, I did believe that if you do some accidental clicking in SSMS the change script will sometimes create the monster scripts instead of the simple add column.
To answer the question posed by the OP, I see this process being more complicated and risky than simply adding columns. It does not make sense that MS would create a statement to add columns, if it is not best practice to use it.
March 27, 2008 at 11:09 am
It's all about column positioning.
With an ALTER statement, you cannot specify an ORDINAL_POSITION so the column will always be added to the end of the table. If you want a column in the middle of the table, you have to create a new table and copy the table contents.
I have gotten into the practice of putting the auditing fields (CreateDate, ModifiedDate, etc.) at the beginning of the table so adding new columns with an ALTER script does not look odd.
March 27, 2008 at 11:24 am
Good point Michael. If positioning of the column is needed, the alter statement will not help you.
March 27, 2008 at 11:41 am
All,
Thanks for the replies. So far, it seems that the only real benefit to the "monster" script is that it allows ordinal positioning of the new column.
As for the SSMS creating the script this way, that may be the case, but this developer writes these scripts by hand. This is why I first became flabbergasted by his method: I wrote the simple ALTER TABLE script and he insisted that I hand-write the monster script (for a table with about thirty columns... Don't get me started on the normalization problems in these tables.
Chris Judge
March 27, 2008 at 1:11 pm
I feel obliged, for the sake of accuracy, to point out that although your co-workers steps for adding a column to a table bear an eerie similarity to the scripts generated by SSMS, they are not the same and in fact are markedly inferior to them. The actual steps that SSMS follows are:
A. Start a transaction.
B. Create a tmp_* table with the new structure.
C. Copy data from the old table to the Tmp table.
D. Drop the old table.
E. Rename the tmp table to the original name.
F. Alter the new table to add the primary key.
G. Commit the transaction.
I have included an actual example below:
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
--A. does have a transaction:
BEGIN TRANSACTION
GO
--B. Creates Temp table with NEW structure:
CREATE TABLE dbo.Tmp_MSTierServices
(
TierSvcsID int NOT NULL IDENTITY (1, 1) NOT FOR REPLICATION,
Foobar nchar(10) NULL,
TierID int NOT NULL,
ServiceID int NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_MSTierServices ON
GO
--C. Copies data from original table to Temp table
IF EXISTS(SELECT * FROM dbo.MSTierServices)
EXEC('INSERT INTO dbo.Tmp_MSTierServices (TierSvcsID, TierID, ServiceID)
SELECT TierSvcsID, TierID, ServiceID FROM dbo.MSTierServices WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_MSTierServices OFF
GO
--D. Drop the old table
DROP TABLE dbo.MSTierServices
GO
--E. rename to Temp table to the original name
EXECUTE sp_rename N'dbo.Tmp_MSTierServices', N'MSTierServices', 'OBJECT'
GO
--F. ALTER table to add Primary Key:
ALTER TABLE dbo.MSTierServices ADD CONSTRAINT
PK_MSTierServices PRIMARY KEY CLUSTERED
(
TierSvcsID
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
--G. Commit:
COMMIT
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 27, 2008 at 1:41 pm
I feel obliged, for the sake of accuracy, to point out that although your co-workers steps for adding a column to a table bear an eerie similarity to the scripts generated by SSMS, they are not the same and in fact are markedly inferior to them. The actual steps that SSMS follows are:
A. Start a transaction.
B. Create a tmp_* table with the new structure.
C. Copy data from the old table to the Tmp table.
D. Drop the old table.
E. Rename the tmp table to the original name.
F. Alter the new table to add the primary key.
G. Commit the transaction.
This is not accurate.
The steps MS performs are:
1. drop constraints
2. create temp table
3. copy data
4. drop table
5. rename temp table
6. create keys and indexes back
7. add constraints back
8. commit
How is this inferior? The only thing it needs is a little tweaking to check for existence and error logging.
March 27, 2008 at 9:59 pm
Adam Haines (3/27/2008)
I feel obliged, for the sake of accuracy, to point out that although your co-workers steps for adding a column to a table bear an eerie similarity to the scripts generated by SSMS, they are not the same and in fact are markedly inferior to them. The actual steps that SSMS follows are:
A. Start a transaction.
B. Create a tmp_* table with the new structure.
C. Copy data from the old table to the Tmp table.
D. Drop the old table.
E. Rename the tmp table to the original name.
F. Alter the new table to add the primary key.
G. Commit the transaction.
This is not accurate.
The steps MS performs are:
1. drop constraints
2. create temp table
3. copy data
4. drop table
5. rename temp table
6. create keys and indexes back
7. add constraints back
8. commit
How is this inferior? The only thing it needs is a little tweaking to check for existence and error logging.
Adam, I never said that this list was inaccurate or inferior. In fact your list and mine are substantially the same: you include the DROP CONSTRAINTS which I left out because I felt it was not relevant to this particular discussion and you do not include the Transaction which I did because I felt that it was especially germane.
What I was actually referring to was the original list of steps (promoted by his co-worker) which is:
1. create a temp table with same structure as the existing table
2. copy all records from existing table to temp table via an INSERT statement
3. drop the existing table
4. create a new table with the old structure plus the new column
5. add the primary key constraint to the new table
6. copy the data from the temp table to the newly created table again with an INSERT
7. add other indexes to the newly created table
8. drop the temp table
Although this initially looks this same as our lists, you will note the following very significant differences:
I. Temp table has the original structure, but in the MS script the Tmp table has the new structure, which is better.
II. The data is copied twice, but the MS script only copies the data once, which is better.
III. The PK is added before the final data copy, but in the MS script the PK is added after all data copies, which is better.
IV. There is no transaction (also noted by the OP), but the MS script does have a transaction, which is better.
Thus my conclusion that the original list was inaccurate and inferior to the actual MS script.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 28, 2008 at 6:42 am
Rbarryyoung,
Thanks for the reply :). My post wasnt meant to pick at your or say you were wrong. I misinterpreted your post by thinking that you were saying the MS scripts did not do things such as rebuild indexes or constraints. I compeletey agree with your logic here. As always, you have a given detailed reasoning for your opinion, which I sometimes fail to do.
Thanks again for the detailed post and sorry for any misconceptions.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply