형 변환과 Callate

통계나 분석을 위한 쿼리를 작성하다 보면 PIVOT과 UNPIVOT 등의 함수를 사용하는 경우가 발생하게 된다.

이 때, UNPIVOT 시 데이터의 형이 서로 다를 시 데이터 타입에러가 발생된다.

-- DB 정보DMV를 이용한 예제이다 SELECT  Catalog_name ,          Catalog_values FROM   (                                 SELECT  [name]                                 ,          [database_id]                                 FROM sys.databases                                 WHERE [name]='AdventureWorks2019'                      )          AS T UNPIVOT (                                 Catalog_values FOR Catalog_name IN ([name], [database_id])                      )          AS UT

 

위 쿼리 진행 시 다음과 같은 에러를 확인 할 수 있다.

 

따라서 작업 중 대부분의 데이터 타입이 Casting 가능한 문자열로 Casting을 시도하게 된다.

-- 문자열로 캐스팅 SELECT  Catalog_name ,          Catalog_values FROM   (                                 SELECT  cast([name] as nvarchar(max)) as [name]                                 ,          cast([database_id] as nvarchar(max)) as [database_id]                                 FROM sys.databases                                 WHERE [name]='AdventureWorks2019'                      )          AS T UNPIVOT (                                 Catalog_values FOR Catalog_name IN ([name], [database_id])                      )          AS UT

 

문자열로 캐스팅 하였으나 이전과 같은 에러를 확인 할 수 있다.

 

두 컬럼이 모두 문자열로 변경되었는데 해당 오류가 왜 발생되었을까?

한참을 고생하다 다음과 같은 차이를 발견하였다.

 

위의 그림은 SQL Server의 속성 정보이고 아래 그림은 데이터베이스의 속성 정보 이다.

SQL Server으 기본 Collationd이 Korean_Wansung_CI_AS 이고 데이터베스의 Callation은 SQL_Latin1_General_CP1_CI_AS 로 서로 다른 것을 확인할 수 있다.

 

해당과 같이 쿼리를 변경했을 시 해당 문제가 해결이 되었다.

SELECT    Catalog_name ,             Catalog_values FROM     (                                         SELECT    cast([name] collate database_default as nvarchar(max)) as [name]                                         ,             cast([database_id] as nvarchar(max)) as [database_id]                                         FROM sys.databases                                         WHERE [name]='AdventureWorks2019'                            )            AS T UNPIVOT (                                         Catalog_values FOR Catalog_name IN ([name], [database_id])                            )            AS UT

 

이로써 문자열로 형 변환 시 (Cast or Convert) 기본적으로 SQL Server의 설정을 따라 변경되게 되고 해당 데이터베이스에 맞게 Callation을 맞춰주어야 원하는 대로 형 변환이 되는 것을 확인할 수 있었다.