[SQL 2005 GUIDE] 테이블 변경 - 열 추가하기

테이블에 열을 추가할 경우, NOT NULL 속성으로 열을 추가할 수는 있지만 이 경우에는 반드시 DEFAULT를 지정해야 합니다. 대용량 테이블에 대해서 NOT NULL 속성으로 열을 추가하면 소요시간이 오래 걸리고 잠금으로 인하여 서비스에 지장이 발생하는 문제가 발생합니다. 7X24 서비스를 제공해야 하는 시스템의 대용량 테이블에 NOT NULL 속성의 열을 추가하고자 하는 경우에는 먼저 NULL 속성(NULL 허용)으로 열을 추가하고, 추가한 열의 값이 NULL인 데이터를 조금씩 나누어서 UPDATE한 다음에 모든 NULL 값을 NULL이 아닌 값으로 업데이트되면 NOT NULL 속성으로 열을 변경하면 서비스에 지장 없이 작업이 가능합니다. 대용량 테이블인 경우에는, NOT NULL 속성으로 열을 추가하면 Sch-M Lock으로 심각한 블로킹을 유발할 수 있다는 점을 꼭 기억하세요. ^ ^

 

정해진 시간 내에 작업을 끝내야 하는 시스템에서 크기가 큰 테이블에 열을 NOT NULL로 추가하였는데, 정해진 시간 내에 ALTER TABLE의 수행이 완료되지 않아서 서비스 장애로 이어지는 불상사가 간혹 빌셍합니다. 테스트 데이터베이스에서 소요시간을 미리 확인하고 소요시간이 제한된 시간을 초과한다면 다음의 팁을 활용하기 바랍니다.

대용량 LargeTabAddNotNullCol 테이블에, 데이터 형식이 char(50), NOT NULL 속성을 가진 NotNullCol 열을 추가하는 스크립트입니다.

[따라하기] NOT NULL 속성 열 추가하기

USE Sample; GO -- 테스트 테이블을 생성합니다. SELECT identity(int,1,1) as Seq, p1.* INTO LargeTabAddNotNullCol FROM AdventureWorks.Production.Product p1              CROSS JOIN AdventureWorks.Production.Product p2; GO -- NULL 속성으로 열을 추가합니다. ALTER TABLE LargeTabAddNotNullCol ADD NotNullCol char(50) NULL; GO -- 업데이터 성능을 위하여 인덱스를 생성합니다. CREATE INDEX IDX_1 ON LargeTabAddNotNullCol (NotNullCol); GO -- 전체 데이터를 분할하여 업데이트합니다. DECLARE @ROWS int SET @ROWS=10000; UPDATE TOP (@ROWS) LargeTabAddNotNullCol SET NotNullCol = 'default value' WHERE NotNullCol IS NULL; WHILE @@ROWCOUNT = 10000 BEGIN     UPDATE TOP (@ROWS) LargeTabAddNotNullCol              SET NotNullCol = 'default value'     WHERE NotNullCol IS NULL; END GO -- NotNullCol 열에 NULL 값이 있는지 확인합니다. SELECT count(*) FROM LargeTabAddNotNullCol WHERE NotNullCol IS NULL -- NotNullCol 열에 존재하는 인덱스를 제거합니다. DROP INDEX LargeTabAddNotNullCol.IDX_1; GO -- NotNullCol 열을 NOT NULL 속성으로 변경합니다. ALTER TABLE LargeTabAddNotNullCol ALTER COLUMN NotNullCol char(50) NOT NULL; GO