COLLATION

COLLATION : 데이터정렬

COLLATION은 문자열의 정렬 순서를 결정하는 중요한 녀석이다. 5개의 항목으로 나눠지는데 세부항목을 따져볼 필요가 있다.

1. 언어 설정

 - Korean_Wansung : 한국어

 - Latin1_General     : 영어

 - Chinese_PRC      : 중국어

 - Japanese            : 일본어

 - ...

2. 대, 소문자 구분

 - CI(CASE Insensitive) : 대, 소문자 구분하지 않음

 - CS(CASE Sensitive)  : 대, 소문자 구분

ex) STRING = string -> CI 면 TRUE CS 면 FALSE

 

3. 악센트 구분

 - AI(Accent Insensitive) : 악센트 구분하지 않음

 - AS(Accent Sensitive   : 악센트 구분

ex a = ấ -> AI 면 TRUE AS 면 FALSE

4. 히라, 가타가나 구분

 - KI(Kana Insensitive) : 일본어 히라, 가타가나 구분하지 않음 (defalut)

 - KS(Kana Sensitive)  : 일본어 히라, 가타가나 구분

ex アエイオウ = あえいおう -> KI 면 TRUE KS 면 FALSE

5. 전, 반자 구분

 - WI(Width Insensitive) : 전, 반자 구분하지 않음 (defalut)

 - WS(Width Sensitive)  : 전, 반자 구분

ex) A -> WI 면 2Byte WS 면 1Byte

일본어 COLLATION의 Japanese_CI_AS_KS_WS 를 분석해보면

일본어 + 대, 소문자 구분안함 + 악센트 구분 + 가나 구분 + 전, 반자 구분

의 조합으로 이루어 졌음을 알 수 있다.

DEFAULT COLLATION은 설치된 윈도우의 영향을 받는다. 한국 윈도우 설치 시 Korean_Wansung_CI_AS 고 영문 윈도우 설치 시 Latin1_General_CI_AS 이 DEFAULT COLLATION이다.

COLLATION 문제를 만날 수 있는 경우 테스트

아래와 같이 테이블 2개를 생성하면서 한 테이블은 데이터베이스 기본 값 다른 데이터베이스는 COLLATION 지정

USE TEST

GO

-- 테이블생성

CREATE TABLE dbo.Collation1

(

    Col1 VARCHAR(10)

,   Col2 INT

)

GO

CREATE TABLE dbo.Collation2

(

    Col1 VARCHAR(10) COLLATE Japanese_CI_AS_KS_WS

,   Col2 INT

)

GO

-- 데이터삽입

INSERT INTO dbo.Collation1

                 SELECT 'A', 1

UNION ALL SELECT 'B', 2

UNION ALL SELECT 'C', 3

GO

INSERT INTO dbo.Collation2

                 SELECT 'A', 11

UNION ALL SELECT 'B', 22

UNION ALL SELECT 'C', 33

GO

 

--*/--

    -- COLLATION Error

    SELECT *

    FROM dbo.Collation1 AS a

    JOIN dbo.Collation2 AS b

    ON a.Col1 = b.Col1

COLLATION이 다른 컬럼을 이용하여 조인 시 <그림1>과 같은 에러를 보이며 실행되지 않는다.

    -- DATABASE_DEFAULT

    SELECT *

    FROM dbo.Collation1 AS a

    JOIN dbo.Collation2 AS b

    ON a.Col1 = b.Col1 COLLATE DATABASE_DEFAULT

 

Column의 COLLATION이 데이터베이스의 COLLATION과 다른 것을 DATABASE_DEFAULT로 설정하고 실행하면 <그림2>에서 보는 것과 같이 문제없이  실행된다 .

-- ALTER COLLATION    

    ALTER TABLE Collation2 ALTER COLUMN Col1 VARCHAR(10) COLLATE Korean_Wansung_CI_AS

   

    SELECT *

    FROM dbo.Collation1 AS a

    JOIN dbo.Collation2 AS b

    ON a.Col1 = b.Col1

Column의 COLLATION이 데이터베이스의 COLLATION과 다른 것을 DATABASE 설정과 같게 설정하고 실행하면 <그림3>에서 보는 것과 같이 문제없이 실행된다.

--*/--

COLLATION 확인명령어

1. SERVER   : SELECT SERVERPROPERTY(N'COLLATION') AS COLLATION

2. DATABASE : EXEC sp_helpdb TEST

(실행 결과가 너무 길어 전체를 보여주기 위해 3줄로 잘랐다.)

3. COLUMN   : EXEC sp_help Collation1

COLLATION 변경 명령어

1. SERVER   : Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ] /SQLCOLLATION=CollationName

-> MSDN

-> 자세한 사항 확인

확인사항

 - 사용자 데이터베이스 백업

 

2. DATABASE : ALTER DATABASE TEST COLLATE Latin1_General_CI_AS

확인사항

 - 변경하려는 데이터베이스를 사용 중인 세션

 

3. COLUMN   : ALTER TABLE Collation1 ALTER COLUMN Col1 VARCHAR(10) COLLATE Latin1_General_CI_AS

확인사항

 - 계산된 열

 - 인덱스

 - 자동으로 또는 CREATE STATISTICS 문에 의해 생성된 배포 통계

 - CHECK 제약 조건

 - FOREIGN KEY 제약 조건

ps1.

SQL Server 2008 R2 Collation

20120405 1 Collation 테스트.sql

20120405 2 Collation Error 테스트.sql