안녕하세요. 박세계입니다.

코드 블럭이 존재할 경우 busy.org에서 제대로 표현하지 못해 글 전체 레이아웃이 깨지는 걸 발견했습니다. 매우 아쉽습니다. 본 글을 온전히 보기 원하시면 공식 사이트 사용을 권합니다.

데이터베이스 쿼리문 작성시 특정 행의 컬럼값 변경 여부를 알아내야 하는 경우가 있습니다. UPDATE시 컬럼값이 실제로 변경된 경우 별도 로그테이블에 남기는 상황말입니다. 좀 더 실질적인 예를 들면, 샵 판매자가 특정 상품의 가격등 중요한 정보를 수정하는 경우 기존 컬럼값을 나중을 위해 로그 테이블에 저장하는 식입니다. 이때 변경 시도만 하고 실제로는 변경사항이 없는 경우까지 모두 로그로 남기는건 비효율적입니다.

복수개의 컬럼이 존재할 경우 값이 변경된 컬럼만 일일이 찾아내서 해당 컬럼값의 변경 이력만 로그로 남기는 경우는 별로 없습니다. 여러 컬럼중 단 한개 컬럼의 단 한자라도 변경되면, 해당 행의 데이터가 변경된걸로 간주해 모든 컬럼값 즉 해당 행 전체의 로그를 남기는 상황을 가정하겠습니다.

컬럼값(들)의 실제 변경 여부를 알아내는게 매우 쉬울거 같지만 SQL 세계에서는 마냥 그렇지가 않습니다. 왜 그런지 같이 살펴보겠습니다.

일반적인 방법 (잠재적 문제 존재)

가장 단순 무식한 방법은 아래와 같이 UPDATE 문 전후에서 컬럼값을 변수에 저장 및 비교하는 것입니다. 이 방법이 일반적이며 사실 딱히 나쁘지 않아보입니다. 하지만, 기대와 다르게 동작할 여지가 있는 좋지 않은 방법입니다.

CREATE TABLE #Method1
(
    Id int PRIMARY KEY,
    Name varchar(20) NOT NULL,
    Date datetime2 DEFAULT(SYSUTCDATETIME()) NOT NULL
)

CREATE TABLE #Method1_Log
(
    LogId int PRIMARY KEY IDENTITY(1,1),
    Id int NOT NULL,
    Name varchar(20) NOT NULL,
    Date datetime2 DEFAULT(SYSUTCDATETIME()) NOT NULL
)

INSERT INTO #Method1 (Id, Name) VALUES (1, 'Segye')

-- @OldName: Segye
DECLARE @OldName varchar(20) = (SELECT Name FROM #Method1 WHERE Id = 1)

UPDATE #Method1
   SET Name = 'SEGYE'
 WHERE Id = 1

-- @NewName: SEGYE
DECLARE @NewName varchar(20) = (SELECT Name FROM #Method1 WHERE Id = 1)

IF @OldName = @NewName -- Segye = SEGYE?
    PRINT 'Same' -- Actual Result (WHY????????????)
ELSE
    PRINT 'Different' -- Expected Result

위 방법의 문제점은 다음과 같이 크게 두가지가 있습니다.

문제점 1

SQL Server 설치시 COLLATE의 기본 설정값은 Latin1_General_CI_AS 입니다. 즉, Case-Insensitive가 기본이라 비교시 대소문자 구분을 하지 않습니다. Segye와 SEGYE는 엄연히 다른 문자열인데 알다시피 둘을 같은 값으로 간주합니다. 데이터 변경 여부를 알아내려고 하는 상황에서 기대하는 결과는 아닙니다.

해결 방법은 다음과 같이 비교문 뒤에 COLLATE Latin1_General_CS_AS를 추가하여 Case-Sensitive로 임시 설정을 하는것 입니다. 해결이 되긴 되지만 매우 구린게 확실합니다.

IF @OldName = @NewName COLLATE Latin1_General_CS_AS
    PRINT 'Same'
ELSE
    PRINT 'Different' -- Actual&Expected Result (OK)

문제점 2

위의 상황을 해결했다 치더라도, 비교할 대상이 여러개일때 (예: Name, School, Phone, Address, Mother, Father, Gentleman...) 변수 설정 부분과 IF문의 복잡도가 증가합니다. 즉, 특정 행에 있는 모든 컬럼값 중 단 한글자라도 변경되는 경우를 알려면 모든 컬럼값의 전과 후를 일일이 비교 해야 하는데, 완벽한 코드를 짜는게 쉽지 않습니다.

완벽하게 작성했다 치더라도, 이후 컬럼이 하나씩 추가 혹은 삭제 될때마다 쿼리문을 수정해야 하며, 단 한순간이라도 깜빡하면 말짱 도로묵입니다.

해결 방법

이럴때 쓰라고 있는게 바로 BINARY_CHECKSUM (Transact-SQL)입니다. (SQL Server 2008 이상 지원)

BINARY_CHECKSUM(*)을 사용하면 해당 행 모든 컬럼값의 체크섬을 Case-Sensitive하게 계산하여 정수형(int)으로 리턴해줍니다. CHECKSUM(*)도 있는데 Case-Insensitive하게 계산하는 차이점이 있습니다. BINARY_CHECKSUM(*)을 사용할 일이 훨씬 많을거라 생각합니다.

이를 사용한 쿼리문을 보시겠습니다.

CREATE TABLE #Method2
(
    Id int PRIMARY KEY,
    Name varchar(20) NOT NULL,
    Address nvarchar(100) NOT NULL,
    Date datetime2 DEFAULT(SYSUTCDATETIME()) NOT NULL
)

CREATE TABLE #Method2_Log
(
    LogId int PRIMARY KEY IDENTITY(1,1),
    Id int NOT NULL,
    Name varchar(20) NOT NULL,
    Address nvarchar(100) NOT NULL,
    Date datetime2 DEFAULT(SYSUTCDATETIME()) NOT NULL
)

INSERT INTO #Method2 (Id, Name, Address) VALUES (1, 'Segye', 'Seoul, South Korea')

-- @OldChecksum: 2137004977
DECLARE @OldChecksum int = (SELECT BINARY_CHECKSUM(*) FROM #Method2 WHERE Id = 1)

UPDATE #Method2
   SET Name = 'SEGYE' -- Change [Name] only
 WHERE Id = 1

-- @NewChecksum: 2101492657
DECLARE @NewChecksum int = (SELECT BINARY_CHECKSUM(*) FROM #Method2 WHERE Id = 1)

IF @OldChecksum = @NewChecksum
    PRINT 'Same'
ELSE
    PRINT 'Different' -- Actual&Expected Result

Name과 Address 컬럼 중 편의상 Name 컬럼값만 위와 동일하게 Segye에서 SEGYE로 변경해봤습니다. 값이 변경되었으니 BINARY_CHECKSUM(*)의 값이 달라졌음을 볼 수 있습니다. 참고로, 위에서 보이는 2137004977과 2101492657는 랜덤한 값이 아닙니다. 위의 쿼리를 실행할 경우 항상 같은 값을 확인할 수 있습니다. Name 컬럼값을 SEGYE에서 Segye로 다시 변경한다면 체크섬 결과는 다시 2137004977로 돌아옵니다.

특정 컬럼값의 체크섬만 비교하고 싶다면 BINARY_CHECKSUM(Id, Name) 같이 사용하면 됩니다.

BINARY_CHECKSUM 사용시 주의사항

만병통치약같은 BINARY_CHECKSUM(*)이 커버하지 못하는 데이터형이 다섯가지 있습니다. 즉, 다음 데이터형으로 설정된 컬럼값은 체크섬 계산에서 배제됩니다.

image -> varbinary(max)로 변경해서 해결
ntext -> nvarchar(max)로 변경해서 해결
text -> varchar(max)로 변경해서 해결
cursor
xml

image, ntext, text는 어차피 사용을 더이상 권장하지 않기에, 위와같이 차례대로 varbinary(max), nvarchar(max), varchar(max)로 변경해서 해결이 가능합니다. 이거 때문이 아니라도 암튼 바꾸는게 좋습니다. cursor, xml 이렇게 두개의 데이터형만 기억하면 됩니다. 어차피 잘 쓰지도 않는 데이터형입니다.

아쉽게도 다음과 같은 또 하나의 제약이 있습니다.

BINARY_CHECKSUM supports up to 8,000 characters of type varbinary(max) and up to 255 characters of type nvarchar(max).

varbinary(max)는 8,000자 까지만 (즉, 8,000 bytes 까지만), nvarchar(max)는 255자 까지만 지원합니다. 이후의 데이터를 체크섬 대상에서 제외를 한다는건지, 해당 사이즈를 넘는 데이터 전체를 체크섬 대상에서 제외한다는 건지는 명확하지 않습니다. 어차피 두 상황에서 제대로 안되는 건 확실하니 인지하고 있어야 합니다. varbinary(max)는 바이너리 데이터라 딱히 사용할 일이 많지 않은걸 감안하면, nvarchar(max)는 255자 까지만 지원한다는 사실을 잘 기억해둬야 하는데 뭔가 어중간해서 좀 아쉬움이 남긴 합니다. 그래도 4,000자 이내의 유니코드 문자열이 저장되는게 확실하다면 nvarchar(4000) 식으로 해결 가능합니다.

더 자세한 사항을 알기 원하시면 공식문서 BINARY_CHECKSUM (Transact-SQL)을 확인 바랍니다.

감사합니다.

[SQL Server] 컬럼값 변경 여부 알아내기 | Ecency