DROP ... CREATE vs ALTER
当谈到创建存储过程，视图，函数等时，最好是做一个DROP ... CREATE或ALTER对象？DROP ... CREATE vs ALTER
我见过无数的“标准”文件，声明做DROP ...创建，但我看到许多支持ALTER方法的评论和论据。
ALTER方法保留了安全性，但我听说DROP ... CREATE方法在第一次执行时强制重新编译整个SP，而不仅仅是重新编译语句级别。
有人可以告诉我，如果有其他的优势/劣势使用一个在另一个？
ALTER也会强制重新编译整个程序。语句级别重新编译适用于程序中的语句，例如。一个SELECT，因为基础表发生变化而重新编译，而不会对该过程进行任何更改。在ALTER过程中，为了理解什么在SQL文本中发生了变化，服务器将不得不...编译它，甚至不可能有选择地重新编译ALTER过程中的某些语句。
对于所有对象，ALTER总是更好，因为它保留了所有安全性，所有扩展属性，所有依赖性和所有约束。
更正了错字：“stetemnts”到“语句” – 2009-10-29 16:57:39
+1解决了我在问题中提出的两个问题/要点。 – NYSystemsAnalyst 2009-10-29 18:33:07
改变通常更好。如果您删除并创建，则可能会失去与该对象关联的权限。
Nono！ WITH RECOMPILE告诉SQL Server在每次执行**时都抛出查询计划。接下来运行sproc时，所有ALTERS都会导致重新编译。 – Andomar 2009-10-29 16:47:51
在Andomar的辩护中，我在发表评论后改变了答案。他正在写信，我正在回答问题的另一部分，我的回答不清楚。 – kemiller2002 2009-10-29 18:19:45
您已经提出了一个专门涉及不包含任何数据的DB对象的问题，理论上不应该经常更改。
可能您可能需要编辑这些对象，但不是每5分钟一次。正因为如此，我认为你已经击掌了头 - 权限。
简短的回答，而不是一个真正的问题，只要权限是不是一个问题
这是我们如何做到这一点：
if object_id('YourSP') is null
exec ('create procedure dbo.YourSP as select 1')
go
alter procedure dbo.YourSP
as
...
的代码创建了一个“存根”的存储过程，如果它不还没有存在，否则它会改变。通过这种方式，即使您重复执行脚本，该过程的任何现有权限都会保留。
+1不错的技巧.. – 2009-10-29 16:57:14
更好的做object_id（'dbo.YourSP'），否则你可能最终会改变一个表，除了与另一个所有者不存在 – MartW 2009-10-29 17:05:21
CREATE OR ALTER将会非常适合SQL Server .... ... – 2009-10-29 17:36:19
从可用性的角度来看，下降和创建比改变更好。在不包含该对象但具有IF EXISTS DROP的数据库中，Alter将失败，然后CREATE将在具有该对象的数据库中工作，或者在该对象不存在的数据库中工作。在Oracle和PostgreSQL中，通常使用CREATE OR REPLACE创建函数和过程，它与SQL SERVER IF EXISTS DROP相同，然后是CREATE。如果SQL Server拿起这个小而非常方便的语法，那将会很不错。
这就是我该怎么做的。将所有这些放在一个给定对象的脚本中。
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
你能解释一下吗？我很好奇这是不是有价值的？ – Kuberchaun 2009-10-30 21:24:45
我认为你得到-1是因为你的陈述被另一个问题驳斥了。基本上有人不同意你的看法。 – jcollum 2010-04-09 18:32:09
如果您有一个函数/存储过程，例如从网站非常频繁地调用，它可能会导致问题。
存储过程将被丢弃几毫秒/秒，在此期间，所有查询都将失败。
如果你做了改变，你没有这个问题。
新创建的存储过程的模板，通常是这种形式：
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = '<name>')
BEGIN
DROP PROCEDURE <name>
END
GO
CREATE PROCEDURE <name>
......
然而，相反的是更好的，IMO：
如果storedproc /功能/等不存在，创建它与一个虚拟选择语句。然后，修改将始终工作 - 它永远不会被丢弃。
我们有专门的存储过程，所以我们存储的特效/函数通常是这样的：
EXEC Utils.pAssureExistance 'Schema.pStoredProc'
GO
ALTER PROCECURE Schema.pStoredProc
...
，我们使用功能相同的存储过程：
EXEC Utils.pAssureExistance 'Schema.fFunction'
GO
ALTER FUNCTION Schema.fFunction
...
在Utils.pAssureExistance我们做一个IF并查看“。”后面的第一个字符：如果它是“f”，我们创建一个虚拟函数，如果它是“p”，我们创建一个虚拟存储过程。
但要小心，如果您创建了虚标量函数，并且您的ALTER位于表值函数上，那么ALTER FUNCTION将失败，并说它不兼容。
同样，Utils.pAssureExistance才能得心应手，另外还有可选参数
EXEC Utils.pAssureExistance 'Schema.fFunction', 'TableValuedFunction'
将创建一个虚拟的表值函数，
Additionaly，我可能是错的，但我认为，如果你做一个放置过程，并且当前正在使用存储过程的查询，它将失败。
但是，alter procedure将等待所有查询停止使用存储的proc，然后对其进行更改。如果查询将存储过程“锁定”了太长时间（比如几秒钟），ALTER将停止等待锁定，并改变存储的过程：使用存储过程的查询可能会失败。
为什么不只是检查“sys.procedures”而不是使用sysobjects（已弃用）并且必须指定类型？ – 2009-10-29 17:17:15
“sysobjects”从SQL Server 2008开始被弃用：http://msdn.microsoft.com/en-us/library/ms143729.aspx – 2009-10-29 17:37:16
呃...我使用SQL Server 2008中的模板创建了一个存储过程，并且复制粘贴它。 微软应该按照我自己的指导原则！ – Kevin 2009-10-30 00:47:15
DROP通常会丢失权限和任何扩展属性。
在某些UDF上，ALTER也将失去扩展属性（绝对在SQL Server 2005多语句表值函数中）。
我通常不会DROP和CREATE除非我也重新创建这些东西（或知道我想失去它们）。
当我们在开发中工作时，我们曾经使用alter或者创建新的功能或者修改功能。当我们完成我们的开发和测试后，我们会做一个下降和创建。这可以修改特效日期/时间戳，以便您可以按日期/时间对它们进行排序。
它也使我们能够看到我们发送的每个可交付成果的日期是多少。
我不知道是否可以做出这样的评论并说“ALTER更好”。我认为这一切都取决于情况。如果你需要这种细粒度的权限到过程级别，你可能应该在一个单独的过程中处理这个问题。有必要删除和重新创建。它清除了现有的安全性并将其重置为可预测的。
我一直首选使用drop/recreate。我还发现将它们存储在源代码管理中更容易。而不是做....如果存在做改变，如果不存在做创建。
说了这些...如果你知道你在做什么...我不认为这太重要。
如果存在则更好，因为如果在将脚本移动到QA或测试或生产时有多个环境，则不知道脚本是否已在该环境中存在。通过添加一个drop（如果它已经存在）并且然后添加你将被覆盖，不管它是否存在。然后，您必须重新申请权限，但最好听到您的安装脚本错误。
从SQL Server 2016 SP1开始，现在可以选择使用存储过程，函数，触发器和视图的CREATE OR ALTER语法。请参阅SQL Server数据库引擎博客上的CREATE OR ALTER – another great language enhancement in SQL Server 2016 SP1。例如：
CREATE OR ALTER PROCEDURE dbo.MyProc
AS
BEGIN
SELECT * FROM dbo.MyTable
END;
后续问题：是DROP ... CREATE还是原子吗？我假设ALTER是原子的，因为它是一个单独的声明，但也值得验证。 – gzak 2017-04-20 23:41:30