RiCo技術農場

前言

如果環境中每個 SQL Instance(Database) 中相關物件都有所互相參考，

那麼維持這幾個 SQL Instance(Database) 定序一致性可說是相當重要的一件事。

 

定序作用

SQL Server中的定序會提供資料排序規則、大小寫和相關屬性，

定序不只會套用至使用者資料庫資料表中的資料，

還會套用至SQL Server所有中繼資料、暫存物件和變數名稱..等。

 

對於Windows 和 SQL 定序不太懂的朋友可以參考下面連結。

比較Windows 和 SQL 定序比較

(一般來說二進位定序的效能較佳，因為相對簡化且不複雜。)

 

伺服器層級定序

預設定序是在 SQL Server 安裝期間設定，因此也會成為系統資料庫的預設定序。

SELECT CONVERT (varchar, SERVERPROPERTY('collation'))

--查詢伺服器的所有可用定序
SELECT * from ::fn_helpcollations()

當建立資料庫時，您可以使用 CREATE DATABASE 陳述式的 COLLATE 子句來指定預設資料庫定序。
如果未指定任何定序，資料庫就會被指派 model 資料庫的預設定序。
model 資料庫的定序與 SQL Server 執行個體的預設定序相同。
--利用ALTER DATABASE 變更定序
ALTER DATABASE myDB COLLATE Greek_CS_AI




 
--查詢資料庫的目前定序
SELECT CONVERT (varchar, DATABASEPROPERTYEX('database_name','collation'))



資料行層級定序
當您建立資料表時，可以使用 CREATE TABLE 陳述式的 COLLATE 子句來指定每個字元字串資料行的定序。
若未指定任何定序，就會將資料庫的預設定序指派給此資料行。
--變更資料行的定序
ALTER TABLE myTable ALTER COLUMN mycol NVARCHAR(10) COLLATE Greek_CS_AI



運算式層級定序
運算式層級定序是在執行陳述式時設定的，而且它們會影響傳回結果集的方式。
這樣可讓 ORDER BY 將結果排序成地區設定特定的結果。
使用類似下面的 COLLATE 子句來實作運算式層級定序
SELECT name FROM customer ORDER BY name COLLATE Latin1_General_CS_AI



了解上述四個層級定序後，下面就來模擬定序衝突問題。
目前執行個體和DB定序如下
SELECT CONVERT (varchar, SERVERPROPERTY('collation')) as 'Instance Collate'
SELECT CONVERT (varchar, DATABASEPROPERTYEX('myspace','collation')) as 'DB Collate'


 
 
1.join錯誤
--建立兩個資料表
create table t1(c1 nvarchar(10))
create table ##t1(c1 nvarchar(10))
 
--join兩各資料表
select a.c1,b.c1
from t1 a join ##t1 b
on a.c1=b.c1


 
兩個資料表看上去雖然相同，但因為資料庫和tempdb定序不同(tempdb定序和執行個體相同)，
所以其值實際上不相容。
 
快速解決方法:明確指定暫存資料表定序使用資料庫定序(利用 COLLATE DATABASE_DEFAULT關鍵字)
--建立兩個資料表
drop table t1 
create table t1(c1 nvarchar(10))
drop table ##t1 
create table ##t1(c1 nvarchar(10) COLLATE DATABASE_DEFAULT)
 
--join兩個資料表
select a.c1,b.c1
from t1 a join ##t1 b
on a.c1=b.c1


 
 
2.無效的物件名稱
如果執行個體區分大小寫，則暫存物件(#T)就無法找不到。
use ricotest1
go
CREATE TABLE #t(c1 int);
INSERT INTO #t VALUES(1);
GO
USE master;
GO
SELECT * FROM #T;
GO


  
  
 
參考
定序和國際詞彙
使用 BIN 和 BIN2 定序的指導方針