问题描述

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

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.

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

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?

推荐答案

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

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.

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

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

这篇关于删除...创建与更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！