Alter Procedure VS Drop Create

I had to go back through this article and break it out in terms of Pro’s and Con’s.

Pro’s of using Alter Procedure

  1. Permissions stay intact
  2. ObjectId’s of procedures stay the same
  3. Altering a procedure is transactional, dropping is not
  4. The create date of the procedure remains as original, and modified date gets updated

Pro’s of using Drop / Create for a Procedure

  1. Objects can be rebuilt in a new location using source control (does not need to already exist)
  2. You don’t need to worry if the procedure already exists to determine how to write it

I’ve mostly used drop create in places where I’ve worked, but I’ve just been convinced to use Alter (where applicable) instead.

The script below will show the original procedure’s create date and modified date:

SELECT CREATED, LAST_ALTERED
FROM INFORMATION_SCHEMA.ROUTINES

As for the permissions issue, two things come up. The first is that I don’t set my permissions at the object level. I set them actually at the schema level (for 2005+). This comes in handy because whatever schema you create an object under, automatically inherits those permissions. However, if you have a DBA that sets more granular permissions on the objects, then drop/create will get you in trouble, because those permissions will forever be lost.

Another issue with drop/create, is that in a highly transactional system, if you execute the drop statement and a call comes in prior to the create, you will receive errors. Alter will not do this however because it will acquire a schema lock prior to modifying the procedure.

4 comments
Pinakee Das 12 Sep 2011 at 10:39 pm

Derek,
How do you source control the tables ?

Derek Dieter 12 Sep 2011 at 10:59 pm

Hi Pinakee,

While I do put tables into source control, I don’t continue to update them. There is no technical reason I don’t update them, I’m just lazy 🙂

The way I look at it, the backups provide source control for my tables. I do think it is very important to keep all other objects for the database in source control. I usually keep the same folder structure that’s in SSMS.

Derek

the sqlist 21 Oct 2010 at 9:37 am

“The only advantage to Alter procedure over drop/create is that when you alter a procedure, you are able to see when the procedure was initially created in the system, and when it was last modified.”

Not only that, I may say, also the fact that the permission stay in place as opposed when it is dropped.

Derek Dieter 21 Oct 2010 at 11:40 am

That’s true. I forgot about that one. Lately I have been assigning permissions to schemas rather than at the procedure level. That way when you compile a procedure into a particular schema, it automatically inherits the schema’s permissions.

Featured Articles

 Site Author

  • Thanks for visiting!