無法解析 equal to 作業中...之間的定序衝突(Cannot resolve the collation conflict between...in the equal to operation) ：當資料行的定序不相同，資料表聯結無法正常執行(2)

若資料表聯結(Table Join)對象是「暫存資料表(Temporary Table)」或是「資料表變數(Table Variable)」時，會有以下的注意事項：

(一) 「暫存資料表(Temporary Table)」
定序與 tempdb
tempdb 資料庫在每次 SQL Server 啟動時建立，且預設定序與 model 資料庫相同。

通常與執行個體的預設定序相同。

如果建立使用者資料庫，並指定與 model 不同的預設定序，使用者資料庫的預設定序就會與 tempdb 不同。

所有暫存預存程序或暫存資料表會在 tempdb 中建立及儲存。

這表示暫存資料表中所有隱含的資料行，與暫存預存程序中所有可強迫的常數、變數與參數，都會與建在永久資料表和預存程序中的同等物件具有不同的定序。

(二) 「資料表變數(Table Variable)」
若未指定 collation_definition，資料行就會繼承目前資料庫的定序。

---

請參考以下的範例程式碼：

(一) 「暫存資料表(Temporary Table)」

-- 傳回 SQL Server 支援的全部「定序(collation)」之清單。
SELECT name N'定序', description N'描述' 
FROM fn_helpcollations()
-- WHERE name LIKE 'Chinese_Taiwan%'

-- 查詢各個資料庫的「預設定序(default collation)」
SELECT name N'資料庫', collation_name N'預設定序' 
FROM sys.databases 

-- EX1. 使用暫存資料表，資料表聯結(Table Join)
USE Northwind
GO
CREATE TABLE #Customers01
( CustomerID nchar(5), ContactName nvarchar(30), Phone nvarchar(24) )
GO
INSERT #Customers01
 SELECT CustomerID, ContactName, Phone FROM Northwind.dbo.Customers
GO

-- 使用暫存資料表來執行資料表聯結，產生錯誤，因為 Tempdb 與 使用者資料庫兩者的定序是不同
SELECT *
FROM Northwind.dbo.Orders O INNER JOIN #Customers01 C
ON O.CustomerID =C.CustomerID 

/* 錯誤訊息
訊息 468，層級 16，狀態 9，行 3
無法解析 equal to 作業中 ""Chinese_Taiwan_Stroke_BIN"" 與 "Chinese_Taiwan_Stroke_CI_AS" 之間的定序衝突。
 
Msg 468, Level 16, State 9, Line 3
Cannot resolve the collation conflict between ""Chinese_Taiwan_Stroke_BIN"" and "Chinese_Taiwan_Stroke_CI_AS" in the equal to operation.
*/

DROP TABLE #Customers01
GO

--可能的解決方式
--(1) 修改暫存資料表的「定序」，使用 COLLATE database_default
CREATE TABLE #Customers01
( CustomerID nchar(5) COLLATE database_default
 , ContactName nvarchar(30), Phone nvarchar(24) )
GO

--(2) 修改暫存資料表的「定序」，使用 COLLATE Chinese_Taiwan_Stroke_CI_AS
CREATE TABLE #Customers01
( CustomerID nchar(5) COLLATE Chinese_Taiwan_Stroke_CI_AS
 , ContactName nvarchar(30), Phone nvarchar(24) )
GO

--(3) 使用 COLLATE DATABASE_DEFAULT：使 COLLATE 子句繼承目前資料庫的定序。
SELECT *
FROM Northwind.dbo.Orders O INNER JOIN #Customers01 C
ON O.CustomerID =C.CustomerID COLLATE DATABASE_DEFAULT

--(4) 使用 COLLATE 子句，轉換為定序為：Chinese_Taiwan_Stroke_CI_AS
SELECT *
FROM Northwind.dbo.Orders O INNER JOIN #Customers01 C
ON O.CustomerID =C.CustomerID COLLATE Chinese_Taiwan_Stroke_CI_AS

---

(二) 「資料表變數(Table Variable)」

-- EX2. 使用資料表變數
-- 資料表變數：若未指定 collation_definition，資料行就會繼承目前資料庫的定序。

DECLARE @tbCustomers01 TABLE ( CustomerID nchar(5), ContactName nvarchar(30), Phone nvarchar(24) )

INSERT @tbCustomers01
 SELECT CustomerID, ContactName, Phone FROM Northwind.dbo.Customers

SELECT *
FROM Northwind.dbo.Orders O INNER JOIN @tbCustomers01 C
ON O.CustomerID =C.CustomerID  

---

參考資料：

無法解析 equal to 作業中...之間的定序衝突(Cannot resolve the collation conflict between...in the equal to operation) ：當資料行的定序不相同，資料表聯結無法正常執行；Msg 468, Level 16, State 9, Line 3 (1)
http://sharedderrick.blogspot.com/2011/04/equal-to-cannot-resolve-collation.html

使用定序
http://technet.microsoft.com/zh-tw/library/ms187582.aspx

設定與變更資料行定序
http://technet.microsoft.com/zh-tw/library/ms190920.aspx

COLLATE (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms184391.aspx

資料表 (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms175010.aspx

INF：常見問題集 - SQL Server 2000 - 資料表變數
http://support.microsoft.com/?kbid=305977