Today's SQL Server tip

USING ALTER TO PRESERVE OBJECT DEPENDENCIES V7.0
If you need to make a change to an existing stored procedure, view, 
table, or trigger, consider using the ALTER statement instead of 
dropping and re-creating the object(s). By using the ALTER statement, 
you preserve the permissions on stored procedures and views. More 
importantly, ALTER preserves the dependency links between other objects 
that may reference the changed/altered object.

When you drop and re-create a stored procedure, view, table, or 
trigger, its existence is erased from the system tables and re-created 
with a new objectid. As you are likely aware, the objectid is how SQL 
Server uniquely identifies these objects and is the key to linking 
dependencies.

Consider a table with an extensive list of permissions to many 
different groups. Rather than dropping the table, re-creating it, and 
then having to replace its long list of permissions, it is far more 
efficient and simple to use ALTER.

Below is a list of items that are/are not affected by the ALTER 
statement.

ALTER PROCEDURE, ALTER TRIGGER, ALTER VIEW

*No effect on existing permissions

*No effect on existing dependencies

If the ENCRYPTION or RECOMPILE options were used previously, then these 
options must be included in the ALTER statement.

ALTER VIEW

* Column permissions are maintained only when columns have the same 
name before and after ALTER VIEW is performed.

ALTER TABLE

* No effect on existing permissions

* No effect on existing dependencies

------------------------------------------