問題描述

在創建存儲過程、視圖、函數等時，對對象執行 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.

這篇關于刪除...創建與更改的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！