2014년 5월 12일 월요일

기존 컬럼을 IDENTITY 속성으로 변경하기

테스트를 위해 테이블을 하나 만듭니다.
CREATE TABLE TBL1 (COL1 INT, COL2 DATETIME)
COL1에 IDENTITY 속성을 부여합니다.
ALTER TABLE TBL1 ALTER COLUMN COL1 INT IDENTITY(1,1)
결과는
메시지 156, 수준 15, 상태 1, 줄 1
키워드 'IDENTITY' 근처의 구문이 잘못되었습니다.
기존 컬럼을 IDENTITY열로 수정하는 것은 안됩니다. SQL SERVER의 ALTER TABLE 문법에는 IDENTITY를 추가하는것은 있어도 ALTER는 허용하지 않습니다. 된다구요?? 해보셨다구요?? SSMS로 하면 된다구요?? 예 됩니다. ㅠㅠ 일단 해보겠습니다. 일단 SSMS의 테이블 디자인 모드를 사용하여 COL1에 IDENTITY를 설정합니다.
그리고 나서 저장을 하면... 에러나네요.
근데 에러의 이유가 "테이블을 다시 만들어야 하는 변경 내용 저장 사용 안 함" 옵션을 사용했기 때문이랍니다. 기분이 꺼림칙 하지만 저 옵션을 사용 안하도록 설정합니다.
그런 다음 다시 COL1에 IDENTITY 속성을 설정하고 저장을 해 보았습니다. 됩니다!!!!! 근데 이 과정을 프로파일러로 감시를 해보았습니다. SSMS의 모든 활동은 프로파일러로 감시가 가능한데요. 컬럼에 IDENTITY 속성을 설정하는 동안 프로파일러를 켜 놓고 어떤 일이 벌어지는지 봤더니...
CREATE TABLE DBO.TMP_TBL1(
COL1 INT NOT NULL IDENTITY (1, 1),
COL2 DATETIME NULL)
ON [PRIMARY]

GO

ALTER TABLE DBO.TMP_TBL1 SET (LOCK_ESCALATION = TABLE)

GO

SET IDENTITY_INSERT DBO.TMP_TBL1 ON

GO

IF EXISTS(SELECT * FROM DBO.TBL1)
        EXEC('INSERT INTO DBO.TMP_TBL1 (COL1, COL2)
        SELECT COL1, COL2 FROM DBO.TBL1 WITH (HOLDLOCK TABLOCKX)')

GO

SET IDENTITY_INSERT DBO.TMP_TBL1 OFF

GO

DROP TABLE DBO.TBL1

GO

EXECUTE SP_RENAME N'DBO.TMP_TBL1', N'TBL1', 'OBJECT'
예... 수정하는게 아니었군요 1.Tmp_TBL1을 만들고 2.TBL1의 데이터를 Tmp_TBL1에 부어 넣은 다음 3.기존 TBL1은 DROP 하고 4.Tmp_TBL1의 이름을 수정하는 것이었습니다. 혹시나 해서 TBL1을 부모 테이블로 하는 자식 테이블을 만들고 Foreign Key를 설정한 후 해보면 상황은 더욱 심각합니다. 위의 과정에 포린키를 삭제했다가 다시 설정하는 과정이 포함됩니다. 이 과정을 운영중인 DB에서 실행하면 어떻게 될까요? 해보고 알려주.... 아니 하지 마세요!!! 만약 필요에 의해 IDENTITY컬럼으로 수정해야 할 경우는 어떻게 해야 할까요? 동일한 사양의 테이블을 생성하고 SWITCH하는 방법이 있는데 다음은 그 스크립트입니다.
CREATE TABLE TBL1 (COL1 INT NOT NULL, COL2 DATETIME)

GO

INSERT INTO TBL1
SELECT 1, GETDATE() UNION ALL
SELECT 2, GETDATE() UNION ALL
SELECT 3, GETDATE()

GO

CREATE TABLE TBL2 (COL1 INT NOT NULL IDENTITY(1,1), COL2 DATETIME)

GO 

ALTER TABLE TBL1 SWITCH TO TBL2

GO

DROP TABLE TBL1

GO

EXEC SP_RENAME 'TBL2', 'TBL1'
그런데 만약 TBL1을 참조하는 포린키가 있다면 포린키를 다시 작성하는 작업이 추가되어야 하고 이 작업 역시 만만한 작업은 아니므로 운영중에 실시하면 안됩니다.

댓글 3개:

  1. 내부적인 메커니즘에서 결국 alter은 없는데,
    SSMS 에서 "테이블을 다시 만들어야 하는 변경 내용 저장 안함" 라는 옵션은 약간은 좀 궁색(?) 해보입니다ㅋ
    alter 처럼 보이지만 alter 못하니까 copy 하고 기존껀 drop 하고 copy했던걸 rename 하는걸 "동의"해라!! 라는 뉘앙스에요^^;;

    답글삭제
    답글
    1. SSMS로 테이블 디자인을 변경하다보면 저런 일이 꽤 됩니다.
      그래서 되도록이면 쿼리로 처리하는걸 권장합니다.

      그리고 모델링 툴들을 또 조심해야 하는데
      모델링 툴에서 디자인 변경하고 DB에 적용하는 버튼 누르면 지옥을 경험하게 됩니다.
      만약 DB에 적용할 스크립트를 미리 확인할 수 있다면
      스크립트를 확인하고 하나 하나 차근 차근 적용하는것을 권장합니다.

      삭제
  2. 감사합니다.
    SSMS 에서 "테이블을 다시 만들어야 하는 변경 내용 저장 안함" 라는 옵션
    이거 하나 배웠네요...
    좋은 하루 만드세요

    답글삭제