史丹利好熱

 

模擬直接修改資料庫定序

修改資料庫定序為Chinese_Taiwan_Stroke_90_CI_AS_SC

--修改定序
USE master;  
GO  
ALTER DATABASE db1  
COLLATE Chinese_Taiwan_Stroke_90_CI_AS_SC ;  
GO  

馬上來試試新增補充字集的字進去

use db1
INSERT INTO T1 VALUES(NCHAR(0x28CCE),'3')
SELECT * from t1

補充字是寫進去了，但是同事提到的定序衝突?

試試看把t1 table寫到tempdb #t1，然後join table t1

select * from #t1 a 
  join t1 b
    on a.c2 = b.c2 

果然跳出同事提到的 無法解析 equal to 作業中 "Chinese_Taiwan_Stroke_90_CI_AS_SC" 與 "Chinese_Taiwan_Stroke_CI_AS" 之間的定序衝突。

 

union 也有相同情形

select * from #t1 a 
 union all 
select * from t1 b

 

---

調查定序衝突問題:

1.先查詢tempdb中的temporary table(#t1)定序

USE tempdb 
select object_name(object_id) as tablename, name as columnname,collation_name 
from sys.columns
where collation_name is not null
and object_name(object_id) like '#t1%'

神奇了，怎麼沒有_SC，還是原來資料庫修改前的定序!

記得tempdb中的temporary table的欄位定序應該是參照來源資料!

 

2.趕快來檢查來源資料表t1的欄位設定

USE db1 
select object_name(object_id) as tablename, name as columnname,collation_name 
from sys.columns
where collation_name is not null
and object_name(object_id) like 't1%'

的確有SC啊..

 

3.但透過資料表設計工具觀察，是資料庫預設值

 

4.透過Metadata中的結構描述檢視卻是停在資料庫修改前的定序。

use db1
select TABLE_NAME,COLUMN_NAME,COLLATION_NAME from INFORMATION_SCHEMA.COLUMNS

 

---

研判定序衝突原因:

初步猜測是來源資料表t1實際還是舊的定序，因此寫入temporary table也是舊的定序，但產生執行計畫時，因為資料庫的預設定序的定義已經修改成新定序， 在產生執行計畫階段就認定有定序衝突(collation conflict)而中止。

 

---

解決定序衝突的方式:

1.有一種解法是在T-SQL後指定Collate來統一。

強制指定成Chinese_Taiwan_Stroke_90_CI_AS_SC或是Chinese_Taiwan_Stroke_CI_AS或是database_default(建議這個，指)都可以解決。

select * from #t1 a 
  join t1 b
    on a.c2 = b.c2 COLLATE Chinese_Taiwan_Stroke_90_CI_AS_SC

select * from #t1 a 
  join t1 b
    on a.c2 = b.c2 COLLATE Chinese_Taiwan_Stroke_CI_AS

select * from #t1 a 
  join t1 b
    on a.c2 = b.c2 COLLATE database_default  

select c1,c2 from #t1 a 
 union all 
select c1 COLLATE Chinese_Taiwan_Stroke_90_CI_AS_SC,c2 COLLATE Chinese_Taiwan_Stroke_90_CI_AS_SC from t1 b  

但因為要改很多支t-sql和stored procedure作罷

 

2.第二種解法是修改資料行定序

use db1
ALTER TABLE T1 
 ALTER COLUMN c1  
   nvarchar(10)COLLATE Chinese_Taiwan_Stroke_90_CI_AS_SC 

ALTER TABLE T1 
 ALTER COLUMN c2   
   varchar(10)COLLATE Chinese_Taiwan_Stroke_90_CI_AS_SC

GO  

 

重新產生temporary table並且執行inner join及union all。

if object_id('tempdb..#t1') is not null drop table #t1

use db1  
select * into #t1 FROM t1 


--innser join
select * from #t1 a 
  join t1 b
    on a.c2 = b.c2 

--union
select * from #t1 a 
 union all 
select * from t1 b

 

執行結果集:

 

小結:

• 解決了定序衝突的問題，可以不用很多支T-SQL指令或是stored procedure，但要改資料行屬性。
• 如果table很多，建議就是整個資料庫下的資料表都重建，如果很少，可以使用 INFORMATION_SCHEMA.COLUMNS產生語法修改。

 

參考:

設定或變更資料庫定序

https://msdn.microsoft.com/zh-tw/library/ms175835.aspx

設定或變更資料行定序

https://msdn.microsoft.com/zh-tw/library/ms190920.aspx