模擬直接修改資料庫定序

修改資料庫定序為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" 之間的定序衝突。

uni-on 也有相同情形

select * from #t1 a
 uni-on 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
 uni-on 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及uni-on 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
--uni-on
select * from #t1 a
 uni-on 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

注意

若要檢視 SQL Server 2014 與更早版本的 Transact-SQL 語法，請參閱 舊版文件。

注意

支援 char 和 varchar 資料類型的字碼頁轉換，但不支援 text 資料類型的字碼頁轉換。 不會報告字碼頁轉換期間所遺失的資料。

如果指定的定序或參考物件所使用的定序使用 Windows 不支援的字碼頁，SQL Server會顯示錯誤。

跨兩個不同 SQL Server 資料庫執行查詢指令，出現錯誤訊息如下

無法解析 equal to 作業中 "Chinese_Taiwan_Stroke_CI_AS" 與 "Latin1_General_100_CI_AS" 之間的定序衝突。

發生原因

當查詢兩個以上不同的資料庫，當兩者定序不一致時 Join 查詢資料則會發生這項錯誤。

SQL Server 中定序是在建立資料庫時決定，所有資料表、欄位的定序會參照資料庫的設定。

可以針對單一資料表或欄位調整定序，不過遇到不同定序的欄位比對則需要先轉換為一致的定序。

解決方法

查詢時將外點資料欄位定序轉換跟本地資料庫端的定序一致，轉換指令: collate，參閱 COLLATE (Transact-SQL) 

1. 查詢時轉換定序

串接的欄位時指定轉換的定序，將原本查詢與法修改如下:

SELECT*
FROM DB1.dbo.table1 A, DB2.dbo.table2 B 
WHERE A.PK1 = (B.PK1 collate Latin1_General_100_CI_AS)

2. 修改 View 結構

上述的作法，必須在每次串聯查詢都做一次轉換。

若使用頻繁，可以另外建立一個 View，針對會 Join 的欄位先設定好定序，在爾後查詢就不用逐一轉換。

以下範例僅針對其中一個欄位設定轉換定序，設定愈多欄位是會影響效能，需斟酌設地。參考語法如下：

CREATE VIEW [dbo].[viewTable2]
AS
SELECT
(PK1  collate Latin1_General_100_CI_AS) AS PK1
, COL2
, COL3
, …
FROM DB2.dbo.table2 B

GO

建立好 View 後，原本的查詢可以直接串聯

SELECT*
FROM DB1.dbo.table1 A, viewTable2 B 
WHERE A.PK1 = B.PK1

延伸閱讀

[SQL Server]談談SQL Server的定序(Collation) – from gipi