DROP… CREATE 대 ALTER

DROP… CREATE 대 ALTER

---

저장 프로 시저, 뷰, 함수 등을 만들 때 개체에 대해 DROP ... CREATE 또는 ALTER를 수행하는 것이 더 낫습니까?

DROP ... CREATE를 수행하는 "표준"문서를 많이 보았지만 ALTER 메서드를 옹호하는 수많은 주석과 주장을 보았습니다.

ALTER 메서드는 보안을 유지하지만 DROP ... CREATE 메서드는 문 수준의 재 컴파일이 아니라 처음 실행될 때 전체 SP에서 강제로 재 컴파일을 수행한다고 들었습니다.

다른 하나를 사용하는 데 다른 장점 / 단점이 있는지 누군가가 알려주시겠습니까?

---

ALTER는 전체 프로 시저를 강제로 다시 컴파일합니다. 문 수준 재 컴파일은 프로 시저 내부의 문에 적용됩니다. 프로 시저에 대한 변경없이 기본 테이블이 변경되기 때문에 재 컴파일되는 단일 SELECT. ALTER 프로 시저 이후에 SQL 텍스트에서 변경된 내용 을 이해하기 위해 서버가 컴파일해야하는 ALTER 프로 시저의 특정 명령문 만 선택적으로 재 컴파일하는 것도 불가능 합니다.

모든 개체에 대해 ALTER는 모든 보안, 모든 확장 속성, 모든 종속성 및 모든 제약 조건을 유지하므로 항상 더 좋습니다.

---

이게 우리 방식이야:

if object_id('YourSP') is null
    exec ('create procedure dbo.YourSP as select 1')
go
alter procedure dbo.YourSP
as
...

코드는 아직 존재하지 않는 경우 "스텁"저장 프로 시저를 만들고, 그렇지 않으면 변경을 수행합니다. 이러한 방식으로 스크립트를 반복적으로 실행하더라도 프로 시저에 대한 기존 사용 권한이 유지됩니다.

---

일반적으로 변경하는 것이 좋습니다. 삭제하고 생성하면 해당 개체와 관련된 권한을 잃을 수 있습니다.

---

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;

---

예를 들어 웹 사이트에서 자주 호출되는 함수 / 저장된 proc이있는 경우 문제가 발생할 수 있습니다.

저장된 proc은 몇 밀리 초 / 초 동안 삭제되며이 시간 동안 모든 쿼리가 실패합니다.

변경하면이 문제가 없습니다.

새로 생성 된 저장된 proc의 템플릿은 일반적으로 다음과 같은 형식입니다.

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = '<name>')
    BEGIN
        DROP PROCEDURE <name>
    END
GO

CREATE PROCEDURE <name>
......

그러나 그 반대가 더 좋습니다.

storedproc / function / etc가없는 경우 더미 select 문을 사용하여 만듭니다. 그런 다음 변경은 항상 작동하며 절대 삭제되지 않습니다.

이를위한 저장된 proc이 있으므로 저장된 procs / 함수는 일반적으로 다음과 같습니다.

EXEC Utils.pAssureExistance 'Schema.pStoredProc'
GO

ALTER PROCECURE Schema.pStoredProc
...

함수에 대해 동일한 저장된 proc을 사용합니다.

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'

더미 테이블 반환 함수를 생성합니다.

추가로, 내가 틀렸을 수도 있지만 드롭 프로 시저를 수행하고 쿼리가 현재 저장 프로 시저를 사용하고 있다면 실패 할 것이라고 생각합니다.

그러나 변경 프로시 저는 모든 쿼리가 저장 프로 시저 사용을 중지 할 때까지 기다린 다음이를 변경합니다. 쿼리가 저장된 프로 시저를 너무 오래 (예 : 몇 초) "잠그는"경우 ALTER는 잠금 대기를 중지하고 어쨌든 저장된 프로 시저를 변경합니다. 저장된 프로 시저를 사용하는 쿼리는 해당 지점에서 실패 할 수 있습니다.

---

그런 총괄적인 댓글을 달고 "ALTER is better"라고 말할 수 있을지 모르겠다. 상황에 따라 다 다르다고 생각합니다. 절차 수준까지 이러한 종류의 세분화 된 권한이 필요한 경우 별도의 절차에서이를 처리해야합니다. 삭제하고 재생성해야하는 이점이 있습니다. 기존 보안을 정리하고 예측 가능한 것으로 재설정합니다.

저는 항상 drop / recreate를 선호했습니다. 또한 소스 제어에 저장하는 것이 더 쉽다는 것을 알았습니다. 대신 .... 존재하면 변경하고 존재하지 않으면 생성하십시오.

그렇게 말하면 ... 당신이 뭘하는지 안다면 .. 그다지 중요하지 않다고 생각합니다.

---

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

---

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

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.

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

---

DROP generally loses permissions AND any extended properties.

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

I typically do not DROP and CREATE unless I'm also recreating those things (or know I want to lose them).

---

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.

---

Add with a drop if exists is better because if you have multiple environments when you move the script to QA or test or prod you don't know if the script already exists in that environment. By adding an drop (if it already exists) and and then add you will be covered regardless if it exists or not. You then have to reapply permissions but its better then hearing your install script error-ed out.

---

From a usability point of view a drop and create is better than a alter. Alter will fail in a database that doesn't contain that object, but having an IF EXISTS DROP and then a CREATE will work in a database with the object already in existence or in a database where the object doesn't exist. In Oracle and PostgreSQL you normally create functions and procedures with the statement CREATE OR REPLACE that does the same as a SQL SERVER IF EXISTS DROP and then a CREATE. It would be nice if SQL Server picked up this small but very handy syntax.

This is how I would do it. Put all this in one script for a given object.

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

ReferenceURL : https://stackoverflow.com/questions/1644999/drop-create-vs-alter