Alter an existing object rather than Drop and Create

Posted on by

In this blog I would like to write on a topic that I see many developers (and few DBA’s too) doing without trying to understand or think about the complete picture of what is affected. The task of making changes to an existing database object such as tables, stored procedures,  views etc.

Many times, database objects will have to be modified (altered) at a later time, for a table or view it would be adding new columns or changing the data types of the existing columns. For stored procedures it would be change in logic that needs to be incorporated. There are multiple ways to achieve this, one way is to drop the existing one and create the newer one (most probably for a view or stored procedure), the other way is to alter the existing one. What a user needs to keep in mind is, that when you drop a database object and create the new one, the underlying security permissions get whacked and those users / logins that have been assigned additional permissions to these objects will not have the permissions to carry out their task, they should be reassigned with those permissions on the newly created database.

Hence I feel, it is better to alter the existing one instead of dropping and creating.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

– Bru Medishetty

2 thoughts on “Alter an existing object rather than Drop and Create”

  1. Hi, Just found your site. I find myself always making updates to the db because of the constant change within our org. I’d prefer to just do the updates as you suggest, but there has been times when I wanted to do just that, but the server would not allow me to make that change, saying I need to drop and re-create the table (Say for changing field type). What do we do when this happens.

  2. Michelle,

    I completely understand what are you are talking about. I am sure those situations are not completely avoidable, when there is a need to do a drop and create, all I would do and recommend is going through and look at the dependencies and the security permissions for that object and make sure those permissions are given back once the database objects are created…

Leave a Reply