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?

user_342581 · Answer

Starting with SQL Server 2016 SP1, you now have the option to use CREATE OR ALTER syntax for stored procedures, functions, triggers, and views. See CREATE OR ALTER – another great language enhancement in SQL Server 2016 SP1 on the SQL Server Database Engine Blog. For example:

CREATE OR ALTER PROCEDURE dbo.MyProc
AS
BEGIN
    SELECT * FROM dbo.MyTable
END;

user_1128336 · Answer

I don't know if it's possible to make such blanket comment and say "ALTER is better". I think it all depends on the situation. If you require this sort of granular permissioning down to the procedure level, you probably should handle this in a separate procedure. There are benefits to having to drop and recreate. It cleans out existing security and resets it what's predictable.

I've always preferred using drop/recreate. I've also found it easier to store them in source control. Instead of doing .... if exists do alter and if not exists do create.

With that said... if you know what you're doing... I don't think it matters too much.

user_8283956 · Answer

If you perform a DROP, and then use a CREATE, you have almost the same effect as using an ALTER VIEW statement. The problem is that you need to entirely re-establish your permissions on who can and can’t use the view. ALTER retains any dependency information and set permissions.

DROP...CREATE vs ALTER

12 Answers