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

Altering is generally better. If you drop and create, you can lose the permissions associated with that object.

Answer 2

You've asked a question specifically relating to DB objects that do not contain any data, and theoretically should not be changed that often.

Its likely you may need to edit these objects but not every 5 minutes. Because of this I think you've already hit the hammer on the head - permissions.

Short answer, not really an issue, so long as permissions are not an issue

Answer 3

This is how we do it:

if object_id('YourSP') is null
    exec ('create procedure dbo.YourSP as select 1')
go
alter 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.

Answer 4

From a usability point of view a drop and create is better than a alter. Alter will fail in a database that doesn't contain that object, but having an IF EXISTS DROP and then a CREATE will work in a database with the object already in existence or in a database where the object doesn't exist. In Oracle and PostgreSQL you normally create functions and procedures with the statement CREATE OR REPLACE that does the same as a SQL SERVER IF EXISTS DROP and then a CREATE. It would be nice if SQL Server picked up this small but very handy syntax.

This is how I would do it. Put all this in one script for a given object.

IF EXISTS ( SELECT 1
            FROM information_schema.routines
            WHERE routine_schema = 'dbo'
              AND routine_name   = '<PROCNAME'
              AND routine_type   = 'PROCEDURE' )
BEGIN
    DROP PROCEDURE <PROCNAME>
END
GO


CREATE PROCEDURE <PROCNAME>
AS
BEGIN
END
GO

GRANT EXECUTE ON <PROCNAME> TO <ROLE>
GO

Answer 5

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 6

If you have a function/stored proc that is called very frequently from a website for example, it can cause problems.

The stored proc will be dropped for a few milliseconds/seconds, and during that time, all queries will fail.

If you do an alter, you don't have this problem.

The templates for newly created stored proc are usually this form:

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = '<name>')
    BEGIN
     DROP PROCEDURE <name>
    END
GO

CREATE PROCEDURE <name>
......

However, the opposite is better, imo:

If the storedproc/function/etc doesn't exist, create it with a dummy select statement. Then, the alter will always work - it will never be dropped.

We have a stored proc for that, so our stored procs/functions usually like this:

EXEC Utils.pAssureExistance 'Schema.pStoredProc'
GO

ALTER PROCECURE Schema.pStoredProc
...

and we use the same stored proc for functions:

EXEC Utils.pAssureExistance 'Schema.fFunction'
GO

ALTER FUNCTION Schema.fFunction
...

In Utils.pAssureExistance we do a IF and look at the first character after the ".": If it's a "f", we create a dummy fonction, if it's "p", we create a dummy stored proc.

Be careful though, if you create a dummy scalar function, and your ALTER is on a table-valued function, the ALTER FUNCTION will fail, saying it's not compatible.

Again, Utils.pAssureExistance can be handy, with an additional optional parameter

EXEC Utils.pAssureExistance 'Schema.fFunction', 'TableValuedFunction'

will create a dummy table-valued function,

Additionaly, I might be wrong, but I think if you do a drop procedure and a query is currently using the stored proc, it will fail.

However, an alter procedure will wait for all queries to stop using the stored proc, and then alter it. If the queries are "locking" the stored proc for too long (say a couple seconds), the ALTER will stop waiting for the lock, and alter the stored proc anyway: the queries using the stored proc will probably fail at that point.

Answer 7

DROP generally loses permissions AND any extended properties.

On some UDFs, ALTER will also lose extended properties (definitely on SQL Server 2005 multi-statement table-valued functions).

I typically do not DROP and CREATE unless I'm also recreating those things (or know I want to lose them).

Answer 8

We used to use alter while we were working in development either creating new functionality or modifying the functionality. When we were done with our development and testing we would then do a drop and create. This modifys the date/time stamp on the procs so you can sort them by date/time.

It also allowed us to see what was bundeled by date for each deliverable we sent out.