隨記

定序問題

情境1

當 Join 查詢兩個以上不同的資料庫，其定序不一致時就會發生以下錯誤:

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

解法:

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

情境2

假設 Customers 資料表格裡有 city 欄位，裡面包含了台灣各縣市，當我們 SELECT 這個表格，下了 ORDER BY city 時:

回傳的結果裡台北排在前面，彰化的彰因為筆劃最多所以排最後 => 因為 SQL Server 在台灣地區的預設定序是 Chinese_Taiwan_Stroke_CI_AS

情境3

找出特定 TABLE 裡欄位屬性，有指定定序

SELECT * ,table_name,column_name as '欄位名' , column_default as '默認值'
FROM information_schema.columns
WHERE 1=1
AND table_name = 'LicenseMaster'  --指定 Table Name
AND TABLE_SCHEMA ='license'       --指定 Table Owner
AND COLLATION_NAME is not null

如果想用其他的排序法，ex. 注音，可以下:

SELECT customerid, city FROM dbo.Customers
ORDER BY city COLLATE Chinese_Taiwan_BOPOMOFO_CI_AI

Chinese_Taiwan_BOPOMOFO_CI_AI 代表按照注音排序

什麼是定序?

決定資料庫所使用的字元集、排序的方式

以上述情境1來說，當兩張 table 的 PK1 欄位定序不同時，他會**不知道要怎麼做 join**，所以會報錯

以上述情境2來說，要 order by city 欄位，就是依照此欄位的定序來決定要怎麼排

定序名稱的每一個部份都有其意義

以 [Chinese_Taiwan_Stroke]_CS_AS_WS 來說:

  • [SortRules] 用來識別指定字典排序時套用排序規則之字母或語言的字串。例如 Latin1_General 或 Chinese_Taiwan_Stroke
  • Case Sensitivity: CI 指定不區分大小寫，CS 指定區分大小寫
  • Accent Sensitivity: AI 指定不區分腔調字，AS 指定區分腔調字 (通常用在歐洲語系，如法文)
  • Kana Sensitivity: KS 指定區分假名 (用在日文)
  • Width Sensitivity: WS 指定區分全半形，不寫就表示不區分

BIN 指定要用的二進位排序順序，通常是區分大小寫、全半形

例如: _CS_AS_WS => 區分大小寫、區分腔調字、區分全半形

SQL Server 要怎麼查看定序?

定序有分為3種層級: 伺服器等級、資料庫等級、資料行等級

  • 查看**伺服器**等級定序:
SELECT CONVERT(varchar(256), SERVERPROPERTY('collation'));
  • 查看**資料庫**等級定序:
SELECT name, collation_name FROM sys.databases;
  • 查看**資料行**等級定序: (因下指令的結果會有很多筆，可能造成混淆，所以在此提供使用 SQL 工具的方式)
  • step1. 在要查看的資料表點擊右鍵 -> 設計
  • step2. 點擊想看的欄位，下面的屬性就可看到定序

用語法調整欄位屬性後面要補上定序，不然會變回 DB 預設定序，例如:

ALTER TABLE [dbo].[table] 
ALTER COLUMN [FormNo] nvarchar(80) COLLATE Chinese_Taiwan_Stroke_CI_AS;