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?

Solution 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.

Solution 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.

Solution 3

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

Solution 4

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;

Solution 5

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.

View more solutions

Comments

[Andomar]

Nono! WITH RECOMPILE tells SQL Server to throw out the query plan on every execution of the sproc. All ALTERS result in recompilation when the sproc is run next.

[MartW]

Better do object_id('dbo.YourSP') otherwise you might end up ALTERing a table that doesn't exist except with another owner

[marc_s]

why not just check in "sys.procedures" instead of using sysobjects (which is deprecated) and having to specify type??

[marc_s]

"sysobjects" is deprecated as of SQL Server 2008: msdn.microsoft.com/en-us/library/ms143729.aspx

[kemiller2002]

In Andomar's defense, I altered the answer, after he made a comment. He's write, and I was responding to a different part of the question and my answer was unclear.

[Kevin Doyon]

well... I created a stored proc using the template from SQL Server 2008, and copy-pasted it. Microsoft should follow their own guidelines I guess !

[Abdul Saqib]

Can you please explain the -1? I'm curious how this is not of value?

[jcollum]

I think you got a -1 because your statement was refuted in a different question. Basically someone disagreed with you.

[Danny Rancher]

What about for functions?

[Andrew]

@DannyRancher I use IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('FunctionName') AND xtype IN ('FN', 'IF', 'TF')); makes sure that it exists and that it's a function. Creating a function stub is trickier, since scalar-valued functions, inline-table-valued functions, and multi-statement-table-valued functions are all different types.

[MadSkunk]

@marc_s It's finally happening!

[marc_s]

@MadSkunk: interesting! Thanks for the link !!

[Marco Forberg]

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review

[igelr]

Thanks for advice !

[Felipe Correa]

Unsure of why your answer doesn't have more upvotes. I know most customers are usually in older versions of SQL Server, but this should be way on top for new versions.