SQL 删除...创建与更改

DROP...CREATE vs ALTER

提问by DCNYAM

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

在创建存储过程、视图、函数等时，对对象执行 DROP...CREATE 还是 ALTER 更好？

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.

我已经看到许多“标准”文档声明要执行 DROP...CREATE，但我已经看到许多支持 ALTER 方法的评论和论点。

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.

ALTER 方法保留了安全性，而我听说 DROP...CREATE 方法会在第一次执行时强制对整个 SP 进行重新编译，而不仅仅是语句级别的重新编译。

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

有人可以告诉我使用一个比另一个有其他优点/缺点吗？

采纳答案by Remus Rusanu

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 whatchanged in the SQL text after an ALTER procedure the server would have to ... compile it.

ALTER 还将强制重新编译整个过程。语句级重新编译适用于过程内部的语句，例如。单个 SELECT，由于基础表更改而重新编译，无需对过程进行任何更改。甚至不可能选择性地重新编译 ALTER 过程中的某些语句，以便了解服务器必须在 ALTER 过程后...编译它的 SQL 文本中发生了什么变化。

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

对于所有对象，ALTER 总是更好，因为它保留了所有安全性、所有扩展属性、所有依赖项和所有约束。

回答by Andomar

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.

如果它还不存在，代码会创建一个“存根”存储过程，否则它会进行更改。这样，即使您重复执行脚本，该过程的任何现有权限也将保留。

回答by kemiller2002

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

改变通常会更好。如果您删除并创建，您可能会失去与该对象关联的权限。

回答by Dan Jagnow

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

从 SQL Server 2016 SP1 开始，您现在可以选择使用CREATE OR ALTER存储过程、函数、触发器和视图的语法。请参阅SQL Server 数据库引擎博客上的CREATE OR ALTER – SQL Server 2016 SP1 中另一项出色的语言增强功能。例如：

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

回答by igelr

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

如果执行DROP，然后使用CREATE，则效果与使用ALTER VIEW语句几乎相同。问题是您需要完全重新建立关于谁可以和不能使用该视图的权限。ALTER保留任何依赖信息并设置权限。

回答by sam yi

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.

我不知道是否有可能做出如此笼统的评论并说“ALTER 更好”。我认为这一切都取决于情况。如果您需要这种细化到程序级别的权限，您可能应该在一个单独的程序中处理它。不得不放弃和重新创造是有好处的。它清除了现有的安全性并将其重置为可预测的。

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.

我一直更喜欢使用 drop/recreate。我还发现将它们存储在源代码管理中更容易。而不是做.... 如果存在就改变，如果不存在就创建。

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

话虽如此……如果您知道自己在做什么……我认为这并不重要。

回答by Kevin Doyon

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:

然而，相反的更好，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.

如果storedproc/function/etc 不存在，请使用虚拟select 语句创建它。然后，alter 将始终有效——它永远不会被丢弃。

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.

在 Utils.pAssureExistance 中，我们执行 IF 并查看“.”之后的第一个字符：如果它是“f”，我们创建一个虚拟函数，如果它是“p”，我们创建一个虚拟存储过程。

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.

但是要小心，如果您创建了一个虚拟标量函数，并且您的 ALTER 位于表值函数上，则 ALTER FUNCTION 将失败，说它不兼容。

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

同样，Utils.pAssureExistance 可以很方便，带有一个额外的可选参数

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.

但是，alter 过程将等待所有查询停止使用存储过程，然后更改它。如果查询“锁定”存储过程太长时间（比如几秒钟），则 ALTER 将停止等待锁定，并无论如何更改存储过程：使用存储过程的查询可能会在那时失败。

回答by Cade Roux

DROPgenerally loses permissions AND any extended properties.

DROP通常会失去权限和任何扩展属性。

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

在某些 UDF 上，ALTER还会丢失扩展属性（肯定在 SQL Server 2005 多语句表值函数上）。

I typically do not DROPand CREATEunless I'm also recreating those things (or know I want to lose them).

我通常不会DROP，CREATE除非我也在重新创建这些东西（或者知道我想失去它们）。

回答by Michael Riley - AKA Gunny

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.

我们曾经在开发过程中使用 alter 来创建新功能或修改功能。当我们完成我们的开发和测试后，我们会做一个 drop 和 create。这会修改 proc 上的日期/时间戳，以便您可以按日期/时间对它们进行排序。

它还允许我们查看我们发送的每个可交付成果的按日期捆绑的内容。

回答by JL.

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

您已经提出了一个专门与不包含任何数据的 DB 对象相关的问题，理论上不应经常更改。

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.

您可能需要编辑这些对象，但不是每 5 分钟编辑一次。正因为如此，我认为你已经击中了头 - 权限。

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

简短的回答，不是真正的问题，只要权限不是问题