DROP...CREATE vs ALTER

Question

When it comes to creating stored procedures, views, functions, etc., is it better to do a DROP...CREATE or an ALTER on the object?

I've seen numerous "standards" documents stating to do a DROP...CREATE, but I've seen numerous comments and arguments advocating for the ALTER method.

The ALTER method preserves security, while I've heard that the DROP...CREATE method forces a recompile on the entire SP the first time it's executed instead of just a a statement level recompile.

Can someone please tell me if there are other advantages / disadvantages to using one over the other?

Answer 1

ALTER will also force a recompile of the entire procedure. Statement level recompile applies to statements inside procedures, eg. a single SELECT, that are recompiled because the underlying tables changes, w/o any change to the procedure. It wouldn't even be possible to selectively recompile just certain statements on ALTER procedure, in order to understand what changed in the SQL text after an ALTER procedure the server would have to ... compile it.

For all objects ALTER is always better because it preserves all security, all extended properties, all dependencies and all constraints.

Answer 2

This is how we do it:

if object_id('YourSP') is null     exec ('create procedure dbo.YourSP as select 1') go alter procedure dbo.YourSP as ... 

The code creates a "stub" stored procedure if it doesn't exist yet, otherwise it does an alter. In this way any existing permissions on the procedure are preserved, even if you execute the script repeatedly.