[MSSQL] DB서버/컬럼 레벨 셋팅된 Collation 조회 및 Collation level

DB/MSSQL

[MSSQL] DB서버/컬럼 레벨 셋팅된 Collation 조회 및 Collation level

alien22c 2024. 2. 21. 16:05

728x90

Collation은 데이터에 대한 정렬 규칙과 대/소문자 및 악센트 구분 속성 등을 제공한다.
(char, varchar, nchar, nvarchar 등의 문자 데이터 형식과 함께 사용)

SQL Server 인스턴스의 기본 데이터 정렬(Collation)은 설치 중에 정의되므로
여러 언어를 반영하는 문자 데이터를 저장해야 하는 경우 데이터 정렬 설정을 신중하게 지정해야 한다.

보통 한국에서는 Korean_Wansung_CI_AS를 사용하게 되는데,
배포 지역에 따라 다르게 설정되어야 하는 경우 현지에서 사용하는 Collation을 확인하여 셋팅을 하게 된다.

현재 셋팅된 DB 서버/컬럼 셋팅을 조회해보면 다음과 같다.

-- MSSQL에서 지원하는 Collation 전체 조회
SELECT * FROM fn_helpcollations();

-- 현재 셋팅된 Collation 조회
-- #1.데이터베이스 레벨
SELECT name, collation_name
FROM sys.databases;

-- #2.컬럼 레벨
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLLATION_NAME, DATA_TYPE, CHARACTER_SET_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLLATION_NAME IS NOT NULL;

SELECT OBJECT_SCHEMA_NAME(t.object_id) TABLE_SCHEMA, t.name TableName, c.name ColumnName, TYPE_NAME(user_type_id) AS DataType, collation_name
FROM sys.columns c
inner join sys.tables t on c.object_id = t.object_id
WHERE COLLATION_NAME IS NOT NULL;

-- 서버정보 조회
SELECT  SERVERPROPERTY('MachineName') AS ComputerName,
SERVERPROPERTY('ServerName') AS InstanceName,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Collation') AS Collation
;

참고로, 이러한 Collation level은 다음과 같이 4가지가 있다.

<Collation levels>

1) Server-level collations
: 기본 서버 데이터 정렬은 SQL Server 설치 중에 결정되며, 시스템 데이터베이스 및 모든 사용자 데이터베이스의 기본 데이터 정렬이 됨.

2) Database-level collations
: 데이터베이스를 만들거나 수정할 때 CREATE DATABASE 또는 ALTER DATABASE 문의 COLLATE 절을 사용하여 기본 데이터베이스 데이터 정렬을 지정가능함. 데이터 정렬을 지정하지 않으면 데이터베이스에 서버 데이터 정렬이 할당됨.

  참고)
=> 사용자 데이터베이스의 데이터 정렬을 변경할 경우 임시 테이블에서 쿼리할 때 데이터 정렬 충돌이 발생할 수 있음.
임시 테이블은 항상 인스턴스에 tempdb 대한 데이터 정렬을 사용하는 시스템 데이터베이스에 저장되어 사용자
데이터베이스 간의 문자 데이터를 비교하는 쿼리이며 tempdb 데이터 정렬로 인해 문자 데이터 비교 시 충돌이
발생할 경우 실패할 수 있음.
이 경우 쿼리에 직접 COLLATE 절을 지정하여 이 문제를 해결할 수 있음.

3) Column-level collations
: 테이블을 만들거나 변경할 때 COLLATE 절을 사용하여 각 문자열 열의 데이터 정렬을 지정할 수 있음.
  데이터 정렬을 지정하지 않으면 데이터베이스의 기본 데이터 정렬이 열에 할당됨.

  이 경우 collation에 따라 동작하게 되며, unique 체크를 할 경우 이모지의 경우 중복오류가 발생할 수 있으므로 사전에
적절한 Collation을 셋팅하고 진행이 필요함.

4) Expression-level collations
: SQL문이 실행될 때 설정되며 결과 집합이 반환되는 방식에 영향을 주게 됨. ORDER BY 정렬 결과를 Locale별로 다르게 표시할 수 있음.
  예)SELECT name, collation_name
FROM sys.databases
ORDER BY name COLLATE Latin1_General_CS_AI;

728x90