데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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 실행 오류 케이스도 있으니 참고 하길 바란다.

 

 

[참고자료]

 

 


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1831 SQL Server CREATE TABLE syntax diagrams jevida(강성욱) 2016.09.30 1421
1830 SQL Server 2012 Sp1 설치 이슈 및 해결 jevida(강성욱) 2016.09.30 1582
1829 SSD에서 DBCC CHECKDB 성능 벤치마킹 jevida(강성욱) 2016.09.30 1932
» Collation에 따른 ALTER DATABASE 실패 jevida(강성욱) 2016.09.30 1942
1827 LDF 파일이 잘리지 않는 이유 jevida(강성욱) 2016.09.30 2054
1826 Tempdb 경합 확인 및 해결 (Tempdb Contention) jevida(강성욱) 2016.09.30 2222
1825 SQL Server IO 병목 확인과 오해 jevida(강성욱) 2016.09.30 2512
1824 SQL Version에 따른 sp_prepare 정보 반환 jevida(강성욱) 2016.09.30 1717
1823 DReplay 활성 세션 초과 에러 jevida(강성욱) 2016.09.30 1543
1822 저장 프로시저 내 임시 테이블 사용과 프로시저 재컴파일 jevida(강성욱) 2016.09.30 1872
1821 NUMA 노드와 추척플래그 8048 jevida(강성욱) 2016.09.30 820
1820 온라인 인덱스 리빌드와 조각화 증가 jevida(강성욱) 2016.09.30 1608
1819 VARCHAR(MAX) and NTEXT 쿼리 성능 jevida(강성욱) 2016.09.30 4579
1818 CPU 리소스 상태에 따른 병렬 처리 제한 jevida(강성욱) 2016.09.30 1077
1817 매개변수 값의 변경과 SQL 서버 성능 저하 jevida(강성욱) 2016.09.30 1273
1816 IN 절 사용시 예기치 못한 액세스 위반과 SQL Server 종료 jevida(강성욱) 2016.09.30 1060
1815 SQL 버전과 CLR (.NET Framework 버전에 따른 오류) jevida(강성욱) 2016.09.30 1191
1814 SWITCHOFFSET 내장함수의 잘 못된 예측 - 미리 계산한 값을 쿼리에 연결하여 최적화 하기 jevida(강성욱) 2016.09.30 1331
1813 테이블 반환 매개변수 사용과 SQL 2012의 향상된 캐싱 기능 jevida(강성욱) 2016.09.30 1233
1812 디스크 섹터 크기와 데이터베이스 성능 jevida(강성욱) 2016.09.29 1638





XE Login