서버와 데이터베이스의 Collate(정렬셋)가 틀릴때 쿼리 오류 468

-- SQL Server Collation Checking

select serverproperty('collation')

/* Korean_Wansung_CI_AS */

-- Create database with different collation

CREATE DATABASE [DB_Not_Contained]

CONTAINMENT = NONE

COLLATE French_CS_AI

GO

-- Create 2 tables with 1 being a temp table so it goes to tempdb and uses the server collation and not the database collation

USE [DB_Not_Contained]

GO

CREATE TABLE [DemoCollation]

(DemoCollationNM VARCHAR(100))

GO

CREATE TABLE #DemoCollation

(DemoCollationNM VARCHAR(100))

-- Insert records into both tables

INSERT dbo.DemoCollation

(DemoCollationNM)

VALUES ('Test Join');

INSERT #DemoCollation

(DemoCollationNM)

VALUES ('Test Join');

-- Now query and try and join both tables having 2 different collations

SELECT p.DemoCollationNM

FROM dbo.DemoCollation p

INNER JOIN #DemoCollation d ON p.DemoCollationNM = d.DemoCollationNM

/*

메시지 468, 수준 16, 상태 9, 줄 34

equal to 작업에서의 "Korean_Wansung_CI_AS"과(와) "French_CS_AI" 간의 데이터 정렬 충돌을 해결할 수 없습니다.

*/

-- 해결책은 무엇일까?

-- 각 오브젝트의 Collation 을 확인해보자

use [DB_Not_Contained]
go
sp_help DemoCollation

use tempdb
go
sp_help #DemoCollation

-- 해결해 보자

-- 방법. 1

use [DB_Not_Contained]

go

--Now query and try and join both tables having 2 different collations

-- "COLLATE DATABASE_DEFAULT" 옵션을 추가하면 tempdb 의 오브젝트가 현재 데이터베이스의 Collate 를 따라간다.

SELECT p.DemoCollationNM, d.*

FROM dbo.DemoCollation p

INNER JOIN #DemoCollation d ON

p.DemoCollationNM = d.DemoCollationNM COLLATE DATABASE_DEFAULT

-- 방법. 2

SELECT p.DemoCollationNM, d.*

FROM dbo.DemoCollation p

INNER JOIN #DemoCollation d ON

p.DemoCollationNM = d.DemoCollationNM COLLATE French_CS_AI

-- 방법. 3

drop table #DemoCollation

CREATE TABLE #DemoCollation 

(DemoCollationNM VARCHAR(100) COLLATE French_CS_AI)

INSERT #DemoCollation (DemoCollationNM) VALUES ('Test Join');

SELECT p.DemoCollationNM, d.*

FROM dbo.DemoCollation p

INNER JOIN #DemoCollation d ON

p.DemoCollationNM = d.DemoCollationNM

[참고문서]
SQL SERVER – Fix – Error: Msg 468, Level 16, State 9, Line 1
http://blog.sqlauthority.com/2015/07/20/sql-server-fix-error-msg-468-level-16-state-9-line-1/