[SQL] 데이터 정렬(COLLATE) 변경 방법에 대해 알아보자.

 

데이터 정렬 변경하려는 이유

DB서버에 새로운 DB를 만들고

테이블/프로시져를 다 만들어 놓은 상태에서 실서버 반영까지 완료했다.

 

근데 갑자기 문의가...왔다.

'특정 테이블을 한글로 검색하면 데이터가 조회되지 않는다'라고

하지만 영문을 검색하면 데이터가 조회되는 현상이...

 

이것저것 확인해봤더니,

테이블뿐만 아니라 DB의 데이터 정렬 값이 아래 화면과 같은 SQL_Latin1_General_CP1_CI_AS으로 설정되어 있었다.

일반적으로 우리나라에서 사용하는 데이터 정렬은 Korean_Wansung_CI_AS이다.

 

 

Korean_Wansung_CI_AS에 대해 간략하게 설명해보면,

• Korean_Wansung : 한글
• CI : Case Insensitive의 약자로서, 대/소문자를 구분하지 않는다.
• CS : Case Sensitive의 약자이면서 CI의 반대되는 것으로, 대/소문자를 구분한다.
• AS : Accent Insensitive의 약자로서, 악센트를 구분하지 않는다. 여기서 악센트는 언어의 높낮이를 말한다.(ex> 중국어의 성조)

그래서 결론적으로, Korean_Wansung_CI_AS는 한글이면서 대/소문자 및 악센트를 구분하지 않는다.라는 의미를 가진다.

 

우리나라에서 Korean_Wansung_CI_AS을 많이 사용하기는 하지만,

다양한 COLLATION 정보를 조회하기 위해서 아래 첫 번째 쿼리를 실행해보면 된다.

실행하면 MSSQL에서 정의해둔 COLLATION 종류의 값과 설명을 확인할 수 있다.

두 번째는 쿼리는 현재 DB 서버의 COLLTAION의 DEFAULT 값을 출력해준다.

-- COLLATION 종류 값과 설명
SELECT *
FROM FN_HELPCOLLATIONS();
 
-- DB 서버의 기본 COLLATION 값
SELECT CONVERT (VARCHAR(256), SERVERPROPERTY('collation')); 

 

테이블에 대해서도 검색해보니,

아래 그림처럼 컬럼들의 COLLATION 값도 SQL_Latin1_General_CP1_CI_AS 였다.

특이한 점은 모든 컬럼들의 COLLATION 값이 존재하지는 않다는 점이다.

CHAR, VARCHAR, TEXT, NCHAR, NVARCHAR, NTEXT 해당 데이터 타입만 COLLATION 값이 정의되어 있다는 점 잊지 말자.

 

 

DB 서버 자체 데이터 정렬 값을 바꾸는 것은 내 권한을 벗어나는 것이라 판단(라이브 서버 중지해야 되는 문제)하여

DB / 테이블 2가지 영역의 데이터 정렬 값을 바꾸는 것을 정리해보려 한다.

 

DB 데이터 정렬 바꾸기

1. 디비의 속성을 클릭, 옵션에서 액세스 제한을 SINGLE_USER로 변경한다.

2. 테이블에 걸려 있는 INDEX / UNIQUE / CONSTRAINT 등 모두 삭제한다.

3. 아래 쿼리 실행한다.

ALTER DATABASE DB명
COLLATE korean_wansung_ci_as

4. 2번에 삭제했던 것들에 대해서 다시 생성해 준다.

5. 1번에서 변경했던 액세스 제한을 다시 MULTI_USER로 변경한다.

 

이렇게 보면 어려운 부분이 1도 없다.

하지만 작업을 하면서 예외는 항상 발생하는 법.

 

액세스 제한을 SINGLE_USER로 변경 후, 쿼리를 실행하면 아래와 같은 오류가 발생한다.

데이터베이스 'XXXX'이(가) 이미 열려 있고 한 번에 한 사용자만 사용할 수 있습니다.

이때 아래 첫 번째 프로시져를 실행하여, 열려있는 XXXX 데이터베이스 SPID를 알아내어 KILL 해준다.

EXEC SP_WHO2;
KILL SPID;

그리고 5번에서 GUI로 MULTI_USER로 변경 시에도 오류가 발생할 경우가 있다.

이러한 경우에는 아래 쿼리를 이용해서 액세스 제한을 MULTI_USER로 변경해 주면 된다.

ALTER DATABASE DB명 SET MULTI_USER;

 

테이블 데이터 정렬 변경

테이블 데이터 정렬은 DB 데이터 정렬에 비해 간단하다.

사실 DB 데이터 정렬을 변경할 때, 테이블도 같이 해야 효율적이다.

 

DB 데이터 정렬에서 필요했던 1, 5번인 액세스 제한을 변경할 필요가 없기 때문이다.

해당 테이블에 정의되어 있는 INDEX / UNIQUE / CONSTRAINT 등을 삭제 후 아래 쿼리를 실행하고 삭제한 것을 다시 생성해주면 된다.

ALTER TABLE 테이블명
ALTER COLUMN 컬럼명 컬럼타입
COLLATE korean_wansung_ci_as;

간단한 쿼리지만, 변경할 컬럼과 컬럼타입을 모두 적어야 된다는 점이... 귀찮은 작업이다.

 

사실 개발을 하다 보면 기본으로 설정되어 있는 값이기에 데이터 정렬에 대해 간과하고 넘어갈 수 있을 뻔했다.

특이하게 특정 DB에서만 사용하던 데이터 정렬이 아녔기에, 해당 내용을 공부하고 정리할 수 있었던 좋은 계기가 되었다.(좋은 건지... 나쁜 건지..)