茫茫網海中的冷日
         
茫茫網海中的冷日
發生過的事,不可能遺忘,只是想不起來而已!
 恭喜您是本站第 1682792 位訪客!  登入  | 註冊
主選單

Google 自訂搜尋

Goole 廣告

隨機相片
HoneyMoon_Day4_0083.jpg

授權條款

使用者登入
使用者名稱:

密碼:


忘了密碼?

現在就註冊!

DB研討會 : [轉貼]SQL Server tempdb 定序衝突

發表者 討論內容
冷日
(冷日)
Webmaster
  • 註冊日: 2008/2/19
  • 來自:
  • 發表數: 15771
[轉貼]SQL Server tempdb 定序衝突

[SQL Server] tempdb定序衝突(collation conflict)

延續 SQL Server儲存Unicode補充字集的話題,因為同事的目標資料庫是既有資料庫,沒辦法採用新建立資料庫的方式,後來同事直接把資料庫改成支援補充字集的定序(*_SC),一開始很順利,但使用到tempdb,像是join #table 或是uni-on all #table時發現了定序衝突(collation conflict)問題。

來筆記另外兩種解決定序衝突(collation conflict)的作法:

 

模擬直接修改資料庫定序

修改資料庫定序為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] tempdb定序衝突(collation conflict) | 史丹利好熱 - 點部落
冷日
(冷日)
Webmaster
  • 註冊日: 2008/2/19
  • 來自:
  • 發表數: 15771
[轉貼]SQL Server COLLATE (Transact-SQL)

COLLATE (Transact-SQL)

定義資料庫或資料表資料行的定序,或套用至字元字串運算式時的定序轉換作業。 定序名稱可以是 Windows 定序名稱或 SQL 定序名稱。 如果未在資料庫建立期間指定,則會將資料庫指派SQL Server實例的預設定序。 若未於資料表資料行建立期間指定,會將資料庫的預設定序指派給資料行。

Syntax


COLLATE { <collation_name> | database_default }
<collation_name> :: =
{ Windows_collation_name } | { SQL_collation_name }

注意

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

引數

collation_name 套用至運算式、資料行定義或資料庫定義的定序名稱。 collation_name 僅可以是指定的 Windows_collation_nameSQL_collation_namecollation_name 必須是常值。 collation_name 不可以變數或運算式表示。

Windows_collation_nameWindows 定序名稱的定序名稱。

SQL_collation_nameSQL Server 定序名稱的定序名稱。

database_default 使 COLLATE 子句繼承目前資料庫的定序。

備註

您可以在許多層級指定 COLLATE 子句。 其中包括下列各項:

  1. 建立或變更資料庫。

    您可以使用
    CREATE DATABASEALTER DATABASE 陳述式的 COLLATE 子句來指定資料庫的預設定序。 您也可以在使用 SQL Server Management Studio 建立資料庫時指定定序。 如果您未指定定序,資料庫會指派SQL Server實例的預設定序。

    注意

    僅限 Windows Unicode 定序只能搭配 COLLATE 子句使用,以便將定序套用至資料行層級和運算式層級資料的 ncharnvarcharntext 資料類型。這些無法搭配 COLLATE 子句使用,來定義或變更資料庫或伺服器執行個體的定序。

  2. 建立或變更資料表資料行。

    您可以利用 CREATE TABLEALTER TABLE 陳述式的 COLLATE 子句來指定每個字元字串資料行的定序。 您也可以使用 SQL Server Management Studio建立資料表時指定定序。 如果您沒有指定定序,就會將資料庫的預設定序指派給資料行。

    您也可以利用 COLLATE 子句中的 database_default 選項,指定暫存資料表中的資料行使用連接目前的使用者資料庫 (而非 tempdb) 之定序預設值。

  3. 轉換運算式的定序。

    您可以利用 COLLATE 子句,將字元運算式套用至特定定序。 字元常值和變數會被指派目前資料庫的預設定序。 資料行參考會被指派資料行的定義定序。

識別碼的定序會隨定義的層級而不同。 執行個體層級物件 (如登入和資料庫名稱) 的識別碼會被指派執行個體的預設定序。 資料庫內之物件 (如資料表、檢視和資料行名稱) 的識別碼會被指派資料庫的預設定序。 例如,兩份大小寫不同的同名資料表,可以建立在定序區分大小寫的資料庫中,但不能建立在定序不區分大小寫的資料庫中。 如需詳細資訊,請參閱<
Database Identifiers>。

當連接內容只有一個相關聯資料庫時,可以建立變數、GOTO 標籤、暫時預存程序和暫存資料表,之後當內容切換到另一個資料庫時,可以參考它們。 變數、GOTO 標籤、暫存預存程序和暫存資料表的識別碼都位於伺服器執行個體的預設定序中。

COLLATE 子句僅適用於 charvarchartextncharnvarcharntext 資料類型。

COLLATE 會使用 collate_name 參考要套用至運算式、資料行定義或資料庫定義之 SQL Server 定序或 Windows 定序的名稱。 collation_name 只可以是指定的 Windows_collation_nameSQL_collation_name,同時此參數必須包含常值。 collation_name 不可以變數或運算式表示。

定序通常是用定序名稱來識別,但在安裝程式中除外。 在安裝程式中,您會改為指定 Windows 定序的根定序指示項 (定序地區設定),然後再指定區分或不區分大小寫或腔調字的排序選項。

您可以執行系統函數
fn_helpcollations 來擷取 Windows 定序和 SQL Server 定序的所有有效定序名稱清單:


SELECT name, description
FROM fn_helpcollations();

SQL Server只能支援基礎作業系統所支援的字碼頁。 當您執行相依于定序的動作時,所參考物件的SQL Server定序必須使用電腦上執行的作業系統所支援的字碼頁。 這些動作包括:

  • 當您建立或變更資料庫時,指定資料庫的預設定序。
  • 當您建立或變更資料表時,指定資料行的定序。
  • 當還原或附加資料庫時,作業系統必須支援資料庫的預設定序及資料庫中任何 charvarchartext 資料行或參數的定序。

注意

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

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

範例

A. 在 SELECT 期間指定定序

下列範例會建立簡單的資料表並且插入 4 個資料列。 然後範例會在從資料表選取資料時套用兩個定序,並且示範如何以不同的方式排序 Chiapas


CREATE TABLE Locations
(Place varchar(15) NOT NULL);
GO
INSERT Locations(Place) VALUES ('Chiapas'),('Colima')
, ('Cinco Rios'), ('California');
GO
--Apply an typical collation
SELECT Place FROM Locations
ORDER BY Place
COLLATE Latin1_General_CS_AS_KS_WS ASC;
GO
-- Apply a Spanish collation
SELECT Place FROM Locations
ORDER BY Place
COLLATE Traditional_Spanish_ci_ai ASC;
GO

以下是第一個查詢的結果。


Place
-------------
California
Chiapas
Cinco Rios
Colima

以下是第二個查詢的結果。


Place
-------------
California
Cinco Rios
Colima
Chiapas

B. 其他範例

如需使用 COLLATE 的其他範例,請參閱 CREATE DATABASE 範例 G. 建立資料庫並指定定序名稱和選項ALTER TABLE 範例 V. 變更資料行定序


原文出處:COLLATE (Transact-SQL) - SQL Server | Microsoft Learn
冷日
(冷日)
Webmaster
  • 註冊日: 2008/2/19
  • 來自:
  • 發表數: 15771
[轉貼]解決資料庫查詢時定序衝突

解決資料庫查詢時定序衝突

跨兩個不同 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


原文出處: 解決資料庫查詢時定序衝突
前一個主題 | 下一個主題 | 頁首 | | |



Powered by XOOPS 2.0 © 2001-2008 The XOOPS Project|