드롭...작성 대 변경

드롭...작성 대 변경

저장 프로시저, 뷰, 함수 등을 생성할 때 개체에 대해 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
...

코드는 "stub" 저장 프로시저가 아직 존재하지 않는 경우에는 해당 프로시저장소 프로시저는 "stub" 저장 프로시저를 만듭니다.이렇게 하면 스크립트를 반복적으로 실행하더라도 절차에 대한 기존 사용 권한이 유지됩니다.

Server 2016 SP1을 사용할 수 됩니다.CREATE OR ALTER저장 프로시저, 함수, 트리거 및 보기에 대한 구문입니다.CREATE OR ALTER를 참조하십시오. SQL Server 데이터베이스 엔진 블로그에서 SQL Server 2016 SP1의 또 다른 뛰어난 언어 기능입니다.예:

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

일반적으로 변경하는 것이 좋습니다.삭제하고 생성하면 해당 개체와 연결된 사용 권한이 손실될 수 있습니다.

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

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

만약 당신이 수선을 한다면, 당신은 이런 문제가 없습니다.

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

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

CREATE PROCEDURE <name>
......

하지만, 그 반대가 더 낫습니다, 이모:

저장된 proc/function/등이 존재하지 않는 경우 더미 선택 문으로 작성합니다.그러면, 그 변화는 항상 효과가 있을 것입니다 - 절대로 떨어지지 않을 것입니다.

이를 위한 저장 프로시저가 있으므로 저장 프로시저/기능은 대개 다음과 같습니다.

EXEC Utils.pAssureExistance 'Schema.pStoredProc'
GO

ALTER PROCECURE Schema.pStoredProc
...

그리고 다음과 같은 기능에 동일한 저장 프로시저를 사용합니다.

EXEC Utils.pAssureExistance 'Schema.fFunction'
GO

ALTER FUNCTION Schema.fFunction
...

Utils.p에서Existance에서 IF를 수행하고 "." 뒤의 첫 번째 문자를 확인합니다.: "f"이면 더미 함수를 만들고, "p"이면 더미 저장 프로시저를 만듭니다.

그러나 더미 스칼라 함수를 만들고 ALTER가 테이블 값 함수에 있으면 ALTER FUNCTION이 호환되지 않는다며 실패합니다.

다시, Utils.p.존재 확인은 추가 옵션 매개 변수를 사용하여 편리하게 사용할 수 있습니다.

EXEC Utils.pAssureExistance 'Schema.fFunction', 'TableValuedFunction'

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

추가적으로 제가 틀릴 수도 있지만, 현재 저장된 proc를 사용하고 있는 상태에서 Drop 프로시저를 실행하면 실패할 것 같습니다.

그러나 다른 프로시저는 모든 쿼리가 저장된 프로시저 사용을 중지할 때까지 기다렸다가 변경합니다.쿼리가 저장된 proc를 너무 오랫동안 "잠금"하면(예: 몇 초) ALTER는 잠금 대기를 중지하고 저장된 proc를 변경합니다. 저장된 proc를 사용하는 쿼리는 그 시점에서 실패할 수 있습니다.

DROP일반적으로 사용 권한 및 확장 속성이 손실됩니다.

UDF에서는 UDF로 표시됩니다.ALTER또한 확장 속성이 손실됩니다(SQL Server 2005 다중 문 테이블 값 함수에서는 확실히).

저는 일반적으로 그렇지 않습니다.DROP그리고.CREATE내가 그런 것들을 다시 만들고 있지 않는 한(또는 내가 그것들을 잃고 싶어한다는 것을 알고 있지 않는 한).

그런 포괄적인 코멘트를 하면서 "ALT가 더 좋다"고 말할 수 있을지 모르겠습니다.모든 것은 상황에 따라 다르다고 생각합니다.절차 수준까지 이러한 세분화된 권한이 필요한 경우 별도의 절차로 처리해야 합니다.삭제하고 다시 만들어야 하는 것에는 이점이 있습니다.기존 보안을 제거하고 예측 가능한 보안을 재설정합니다.

항상 드롭/재작성을 선호했습니다.또한 소스 제어에 저장하는 것이 더 쉽다는 것을 알게 되었습니다....을 수행하는 대신, 존재하는 경우에는 변경하고 존재하지 않는 경우에는 작성합니다.

그 말이 나온 김에...네가 뭘 하고 있는지 안다면,별로 중요하지 않은 것 같아요.

Drop을 수행한 다음 CREATE를 사용하면 ALTER VIEW 문을 사용하는 것과 거의 동일한 효과를 얻을 수 있습니다.문제는 보기를 사용할 수 있는 사용자와 사용할 수 없는 사용 권한을 완전히 다시 설정해야 한다는 것입니다.ALTER는 모든 종속성 정보를 유지하고 권한을 설정합니다.

데이터가 포함되지 않은 DB 객체와 관련된 질문을 하셨는데, 이론적으로 그렇게 자주 변경해서는 안 됩니다.

이러한 개체는 5분마다 편집해야 할 수도 있습니다.이것 때문에, 나는 당신이 이미 허락을 받았다고 생각합니다.

권한이 문제가 되지 않는 한 짧은 답변, 실제로는 문제가 되지 않습니다.

우리는 개발 작업 중에 새로운 기능을 만들거나 기능을 수정하는 동안 alter를 사용하곤 했습니다.개발 및 테스트가 완료되면 드롭다운을 수행하여 생성할 수 있습니다.그러면 날짜/시간별로 정렬할 수 있도록 프로시저의 날짜/시간 스탬프가 수정됩니다.

또한 발송한 각 납품물에 대해 날짜별로 번들링된 내용을 확인할 수 있었습니다.

스크립트를 QA로 이동하거나 테스트 또는 생성할 때 여러 환경이 있는 경우 해당 환경에 스크립트가 이미 있는지 알 수 없으므로 존재하는 경우 드롭으로 추가합니다.드롭(이미 존재하는 경우)을 추가한 다음 추가하면 해당 드롭의 존재 여부에 관계없이 적용됩니다.그런 다음 사용 권한을 다시 적용해야 하지만 오류가 발생한 설치 스크립트를 듣는 것보다 더 좋습니다.

사용 편의성 측면에서 드롭 앤 크리에이트가 변경사항보다 낫습니다.Alter는 해당 개체를 포함하지 않는 데이터베이스에서 실패하지만 IF EXPES 드롭이 있는 경우 CREATE는 해당 개체가 이미 존재하는 데이터베이스 또는 개체가 없는 데이터베이스에서 작동합니다.Oracle 및 PostgreSQL에서는 일반적으로 CREATE 또는 REPLACE 문을 사용하여 함수 및 프로시저를 생성합니다. CREATE는 SQL SERVER IF 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

언급URL : https://stackoverflow.com/questions/1644999/drop-create-vs-alter