Collation에 따른 ALTER DATABASE 실패

Collation에 따른 ALTER DATABASE 실패

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

 

Collate는 데이터베이스 정의 또는 열 정의에 적용하여 데이터를 정렬을 정의하거나 문자열 식에 적용하여 데이터 정렬 캐스트를 정의한다.

 

Collate에 대한 수준은 데이터베이스를 생성하거나 변경, 테이블 생성 또는 컬럼 변경 작업을 할때 지정 할 수 있으며 char, varchar, text, nchar, nvarchar, ntext 데이터 형식에 대해서만 적용 할 수 있다.

 

이번 케이스는 ALTER DATABASE의 Collate 변경 작업 중 발생 오류로 다음과 같은 메시지를 출력 하였다.

Msg 1505, Level 16, State 1, Line 1 The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.sysschobjs' and the index name 'nc1′. The duplicate key value is (0, 1, person). Msg 5072, Level 16, State 1, Line 1 ALTER DATABASE failed. The default collation of database 'TestCollationChange' cannot be set to SQL_Latin1_General_CP1_CI_AS.

 

우리는 데이터베이스의 메타데이터에 대한 손상으로 생각하고 CHECKDB 또는 CHECKCATALOG 를 실행하였지만 문제점을 찾을 수 없었다.

 

문제의 원인은 Collate 변경 작업 시 대소문자 구분 옵션 변경으로 인하여 동일한 object 충돌이 발생 하였다. 다음 스크립트를 통하여 확인하여 보자.

 

아래 스크립트를 실행하여 데이터베이스를 생성한다. Collation은 Latin1_General_BIN으로 대소 문자를 구문한다.

CREATE DATABASE [TestCollationChange] ON PRIMARY ( NAME = N'TestCollationChange', FILENAME = N'C:\SQL_Data\TestCollationChange.mdf') LOG ON ( NAME = N'TestCollationChange_log', FILENAME = N'C:\SQL_Data\TestCollationChange_log.ldf') COLLATE Latin1_General_BIN; GO USE [TestCollationChange]; GO CREATE TABLE dbo.Person (RowID int NOT NULL IDENTITY (1, 1), FirstName varchar(30) NOT NULL, LastName varchar(30) NOT NULL); GO ALTER TABLE dbo.Person ADD CONSTRAINT PK_Person PRIMARY KEY CLUSTERED (RowID); GO CREATE TABLE dbo.person (RowID int NOT NULL IDENTITY (1, 1), FirstName varchar(30) NOT NULL, LastName varchar(30) NOT NULL); GO ALTER TABLE dbo.person ADD CONSTRAINT PK_person PRIMARY KEY CLUSTERED (RowID); GO

 

ALTER DATABSE 구문을 이용하여 데이터베이스의 Collation을 SQL_Latin1_General_CP1_CI_AS로 변경한다. 해당 Collation은 대소문자를 구문하지 않는다.

스크립트를 실행하면 다음과 같은 오류를 확인 할 수 있다.

ALTER DATABASE [TestCollationChange] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ALTER DATABASE [TestCollationChange] COLLATE SQL_Latin1_General_CP1_CI_AS;

 

 

대소문자 구분을 하지 않는 옵션에서 동일한 object로 간주되어 충돌이 발생하였다. 다음 스크립트를 이용하여 대소문자에 따른 object 가 있는지 확인해 보자.

SELECT * FROM sys.objects where name = 'person';   SELECT * FROM sys.objects where name COLLATE SQL_Latin1_General_CP1_CI_AS = 'person';

 

 

이처럼 Collate 변경 작업은 데이터베이스의 속성을 변경하는 작업에서 매우 민감한 문제에 속한다. 처음 데이터베이스를 생성하여 사용할 때부터 잘 정의하는 것이 중요하다. 만약 어쩔 수 없는 경우 변경해야 하는 상황이 발생 한다면 인덱스나 오브젝트 등 충돌 가능성에 대한 영향력을 파악 후 작업을 진행 하여야 한다.

 

위와 같은 문제가 있을 경우 해당 테이블을 삭제 또는 변경 후 진행 하여야 한다.

 

대소문자 구분과 관련해서 DMV 실행 오류 케이스도 있으니 참고 하길 바란다.

• Collation에 따른 DMV 실행 오류 : http://sqlmvp.kr/140185365035

 

 

[참고자료]

• http://www.sqlskills.com/blogs/jonathan/alter-database-failed-the-default-collation-of-database-ls-cannot-be-set-to-ls/
• Collation에 따른 DMV 실행 오류 : http://sqlmvp.kr/140185365035
• Collate : http://msdn.microsoft.com/ko-kr/library/ms184391.aspx

 

 

2013-08-02 / 강성욱 / http://sqlmvp.kr