December 15, 2004 at 12:32 pm
If I alter a tables structures to ADD a column by either executing ALTER TABLE or by dropping and recreating the table, what is the impact to dependent objects such as stored procedures, triggers and views? I'm trying to determine if it is always necessary to drop and recreate the dependent objects. I believe in older version of SQL Server, such as 6.0, it was necessary to drop & recreate, but I'm not sure if I need to do this in SQL 2000. The only reason I can think of is I may wish to produce a new query plan and therefor run sp_recompile, but from a functionality point of view a new column shouldn't break any dependent objects.
What are your thoughts?
Thanks, Dave
December 15, 2004 at 3:57 pm
As long as adding is related the recompilaton will happen nexttime your procs is executed automatically. If you use select *.. on your views you will have to execute sp_refreshview to get the change instantly applied to the view. Triggers are just special stored procs and therefore behave the same. If you run sp_recompile on the table all dependent objects are suposed to be recompiled but I personally never tested it.
For Drops though as long as you don't have references to them nor schema binding constraints, it should behave the same as above!
HTH
* Noel
December 15, 2004 at 3:59 pm
You could run into problems in a procedure that is performing inserts. If the column names aren't explicitly called out, all columns will be assumed, and your insert will fail.
Steve
December 16, 2004 at 1:10 am
What I do when changes occur is to build the database from the source code. This means if there are any procedures that may reference the table using select * a mismatch may occur, so the act of re-running the create statement for the referencing object will produce an error. This process saves you from breaking existing code. I've created an application called DB Ghost which deals with this very problem and many more to do with database change management. We also have a white paper on the subject which is well worth the read: http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Approach_To_Do_Change_Mgt.pdf it has been published here as well and recieved very good reviews - check out columnist Darren Fuller.
As a DBA I have never failed a release by using the methodology.
regards,
Mark Baekdal
+44 (0)208 241 1762
Living and breathing database change management for SQL Server
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply