조사 배경

[MSSQL] MacOS에서 기본 환경 셋업에서 Docker 기반으로 MSSQL 2022 on Linux를 설치했다. 처음 설치하고 아무것도 모른 상태로 시작했을 떄, 한글이 제대로 저장되지 않아 당황했다. 검색 이후, 가장 먼저 N-prefix를 활용하는 방법을 익혔지만, 불편해서 접두사 없이도 저장하는 방법이 없는지 조사했다. COLLATE 키워드를 발견하고 테이블 컬럼에 적용했음에도 접두사를 사용해야만 했지만, 데이터베이스 수준에서의 설정을 통하면 접두사를 생략할 수 있음을 발견했다.

본 포스트는 구상한 다양한 경우의 수를 기반으로, 유니코드 문자열의 저장 방식에 대한 실험 결과를 정리하며, 사용한 SQL 스크립트는 git commit으로 공유한다. 아래 수행한 내용과 다소 차이가 있을 수 있으나, 흐름은 비슷하니 스크립트 기반으로 활용할 수 있다.

---

CASE 1

MSSQL의 기본 Collation은 SQL_Latin1_General_CP1_CI_AS이다. 새로운 데이터베이스를 생성할 때 명시하지 않으면 기본적으로 서버의 것이 적용된다. 데이터베이스 Collation이 설정되어 있지 않으므로, 현 상태에서 한글을 저장하기 위해서는 유니코드 문자열로 변환해주는 N-Prefix를 사용해야 한다. 이러한 설정은 일부 테이블의 일부 컬럼에서만 가끔 한글을 사용하는 경우에 적합하다고 생각한다.
참고: collation-and-unicode-support/collation-levels

Table Schema

SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_NAME = 'test_collation'

COLUMN_NAME	DATA_TYPE	COLLATION_NAME
default_col	varchar	SQL_Latin1_General_CP1_CI_AS
default_Ncol	nvarchar	SQL_Latin1_General_CP1_CI_AS
wansung_col	varchar	Korean_Wansung_CI_AS
utf8_col	varchar	Korean_100_CI_AS_SC_UTF8

INSERT Data

INSERT INTO
    test_collation (
        default_col,
        default_Ncol,
        wansung_col,
        utf8_col
    )
VALUES
    -- with N prefix
    (N'with N', N'with N', N'with N', N'with N'), -- 영문
    (N'가나다', N'가나다', N'가나다', N'가나다'), -- 기본 한글
    (N'썛씠덂쒗뙑', N'썛씠덂쒗뙑', N'썛씠덂쒗뙑', N'썛씠덂쒗뙑'), -- 확장 한글
    (N'뀕ㅏㅓㅗ', N'뀕ㅏㅓㅗ', N'뀕ㅏㅓㅗ', N'뀕ㅏㅓㅗ'), -- 자음/모음 조합
    (N'ｶﾀｶﾅ', N'ｶﾀｶﾅ', N'ｶﾀｶﾅ', N'ｶﾀｶﾅ'), -- 반각 카타카나
    (N'カタカナ', N'カタカナ', N'カタカナ', N'カタカナ'), -- 전각 카타카나
    (N'🇰🇷', N'🇰🇷', N'🇰🇷', N'🇰🇷'), -- 이모지
    -- without N prefix
    ('without N', 'without N', 'without N', 'without N'), -- 영문
    ('가나다', '가나다', '가나다', '가나다'), -- 기본 한글
    ('썛씠덂쒗뙑', '썛씠덂쒗뙑', '썛씠덂쒗뙑', '썛씠덂쒗뙑'), -- 확장 한글
    ('뀕ㅏㅓㅗ', '뀕ㅏㅓㅗ', '뀕ㅏㅓㅗ', '뀕ㅏㅓㅗ'), -- 자음/모음 조합
    ('ｶﾀｶﾅ', 'ｶﾀｶﾅ', 'ｶﾀｶﾅ', 'ｶﾀｶﾅ'), -- 반각 카타카나
    ('カタカナ', 'カタカナ', 'カタカナ', 'カタカナ'), -- 전각 카타카나
    ('🇰🇷', '🇰🇷', '🇰🇷', '🇰🇷') -- 이모지
;

Query

id	default_col	default_Ncol	wansung_col	utf8_col
1	with N	with N	with N	with N
2	???	가나다	가나다	가나다
3	?????	썛씠덂쒗뙑	썛씠덂쒗뙑	썛씠덂쒗뙑
4	????	뀕ㅏㅓㅗ	뀕ㅏㅓㅗ	뀕ㅏㅓㅗ
5	????	ｶﾀｶﾅ	????	ｶﾀｶﾅ
6	????	カタカナ	カタカナ	カタカナ
7	????	🇰🇷	????	🇰🇷
8	without N	without N	without N	without N
9	???	???	???	???
10	?????	?????	?????	?????
11	????	????	????	????
12	????	????	????	????
13	????	????	????	????
14	????	????	????	????

결과

데이터베이스 기본 Collation 설정에서

• N 접두사 미사용 시 (8-14행), 영문을 제외한 문자가 ?로 표시됨
• N 접두사 사용하는 경우 (1-7행),
  • 데이터 타입이 NVARCHAR이면 모든 문자가 정상 표시됨
  • 데이터 타입이 VARCHAR이더라도 Collation을 변경하여 호환 가능
    • 단, 인코딩 차이에 의해 Korean_Wansung_CI_AS은 반각 카타카나, 이모지 등의 일부 문자 미지원
    • 레거시 시스템이 아니라면 Korean_100_CI_AS_SC_UTF8을 사용하는 것이 적합하다고 생각

일부 테이블에서 가끔씩만 한글을 사용한다면 Column 수준에서 Collation을 사용하고, 데이터 입력 시 N-Prefix를 활용하자.

---

CASE 2

앞선 경우와는 달리, 영문과 한글의 제약이 없거나 한글 데이터 중심의 구성이라면, 매번 Collation을 설정하는 것도 번거로운 작업이 될 수 있다. 이럴 때 상위 수준에서 Collation을 설정할 수 있다. 서버 수준에서 설정하면 그 값이 데이터베이스와 기본 컬럼에 상위 수준에서 설정한 값을 기본적으로 상속받으므로, 목적에 따라 수준을 선택하면 된다.

앞선 경우와 동일한 스크립트를 수행하되, 서버 수준에서 Collation을 Korean_100_CI_AS_SC_UTF8로 변경했다. 방법은 다음 링크 - MacOS에서 기본 환경 셋업를 참고해도 된다. 링크에서는 Collation을 Korean_100_CS_AS_KS_WS_SC_UTF8로 사용했다는 차이 밖에 없다. 서버 수준 설정 없이 데이터베이스에 설정하려면 다음과 같이 COLLATE 키워드를 사용하면 된다. 테이블

IF NOT EXISTS (
    SELECT *
    FROM sys.databases
    WHERE
        name = 'TestDB'
) CREATE
DATABASE TestDB
COLLATE Korean_100_CI_AS_SC_UTF8;

Collation 설정 여부 확인

SELECT name, collation_name
FROM sys.databases
WHERE
    name = 'TestDB';

SELECT
    COLUMN_NAME,
    DATA_TYPE,
    COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_NAME = 'test_collation';

name	collation_name
TestDB	Korean_100_CI_AS_SC_UTF8

COLUMN_NAME	DATA_TYPE	COLLATION_NAME
default_col	varchar	Korean_100_CI_AS_SC_UTF8
default_Ncol	nvarchar	Korean_100_CI_AS_SC_UTF8
wansung_col	varchar	Korean_Wansung_CI_AS
latin1_col	varchar	SQL_Latin1_General_CP1_CI_AS

Query

앞선 예제와 동일한 방식으로 데이터를 삽입했다. N 접두사를 사용하는 경우는 사용하지 않는 경우와 결과가 동일하다.

id	default_col	default_Ncol	wansung_col	latin1_col
1	with N	with N	with N	with N
2	가나다	가나다	가나다	???
3	썛씠덂쒗뙑	썛씠덂쒗뙑	썛씠덂쒗뙑	?????
4	뀕ㅏㅓㅗ	뀕ㅏㅓㅗ	뀕ㅏㅓㅗ	????
5	ｶﾀｶﾅ	ｶﾀｶﾅ	????	????
6	カタカナ	カタカナ	カタカナ	????
7	🇰🇷	🇰🇷	????	????
8	without N	without N	without N	without N
9	가나다	가나다	가나다	???
10	썛씠덂쒗뙑	썛씠덂쒗뙑	썛씠덂쒗뙑	?????
11	뀕ㅏㅓㅗ	뀕ㅏㅓㅗ	뀕ㅏㅓㅗ	????
12	ｶﾀｶﾅ	ｶﾀｶﾅ	????	????
13	カタカナ	カタカナ	カタカナ	????
14	🇰🇷	🇰🇷	????	????

결과

서버 또는 데이터베이스의 수준에서 Collation을 변경하면 하위 수준의 Collation으로 기본값이 설정된다. 추가로 데이터베이스와 테이블 열의 Collation 호환성에 따라 N-prefix를 사용하지 않더라도 정상적으로 데이터가 입력됨을 확인할 수 있다.

대다수의 테이블에서 한글을 사용한다면 Database 수준에서 Collation을 설정하고, 데이터 입력 시 N-Prefix를 생략할 수 있다.

---

마치며

추가적인 조사 - collation-and-unicode-support를 참고로 Collation과 DataType 설정 기준을 공유하며 마무리한다.

Rule of thumb

1. Collation의 설정은 가능하면 데이터베이스 수준에서 통일한다.
   조인 및 비교 연산 시, 서로 다른 Collation 간 충돌을 사전에 방지할 수 있다.
2. 가능하면 설정된 Collation을 변경하는 일이 생기지 않도록 주의한다.
3. 프로젝트를 처음 시작한다면, 서버 수준에서 Collation을 UTF-8로 통일하는 것도 하나의 방법이다.
4. 데이터 집약적인 서비스를 운영하는 경우, 용량 절감을 위해 다음과 같은 기준으로 인코딩 방식을 구분할 수도 있다.
   • 한 컬럼에 들어갈 데이터에 대해, 영어의 비중이 높다면 UTF8 접미사를 가진 현지 Collation - 예를 들어, Korean_100_CI_AS_SC_UTF8를 사용한다. 이로써 ASCII 문자를 저장할 때, UTF-16보다 1바이트를 절약할 수 있다.
   • 한 컬럼에 들어갈 데이터에 대해, 한글의 비중이 높다면 UTF8 접미사가 없는 일반 Collation - 예를 들어, Korean_100_CI_AS를 사용하는 것을 고려해볼 수 있다. UTF-16 인코딩 사용으로 UTF-8에 비해 한 글자당 1바이트를 절약할 수 있다. 다만,
5. 어떤 종류의 데이터가 들어올지 확정된 상태가 아니라면, 최대한 넓은 범위를 커버하는 Collation(Korean_100_CI_AS_SC_UTF8)과 NVARCHAR(max), 그리고 N-prefix를 활용하여 최대한 보수적으로 시작할 수 있다.