SSMS: Beware of Modify

When you want to change the defintion of a sql programmability object in SQL2008 eg Trigger, Stored Procedure or Function using SQL Server Management Studio, you get to options when right clicking the object, Modify or Script..ALTER to.



I've never really understood the differences between the two as both appear to have the same behaviour of scripting out your object with an ALTER command. But there does appear to be a minor difference, one which i'm sure isn't by design, is only minor but definitely one to be careful of.

When using Modify to script out your object to a new window the GO separator is not included whereas it is included using the Script command. So if you happen to make a few modifications in the same window below the end of the definition of the object to test something "ad hoc", you'll need to remember to highlight only the part of the query window which forms the object. Otherwise, your testing statements will be included as part of object when you just hit F5 to persist the ALTER.

In the following screenshot, i've hit the Modify option and then written an adhoc query for a different purpose. If, I forget to remove the adhoc statement, forget to add a GO statement at the end of the procedure or don't highlight the procedure definition, this test query will be persisted as part of the procedure leading to some undesirable results.