我們上周公司啟動了一個新專案，我們工程師拿到客戶的資料庫之後，發現客戶提供的預存程序無法順利執行，發生了資料庫定序 (collation) 的衝突問題。我們負責的工程師由於是第一次遇到這個狀況，所以鬼打牆了一段時間，因為我上周都在備課，所以我跟他講：「你直接移除重裝 SQL Server 並明確指定定序會比較快，我現在沒時間跟你講太多技術細節。」今天這篇文章我打算來說說這個問題背後的技術細節。
問題描述
首先，我們先拿到客戶提供的資料庫備份檔 (*.bak)，並且還原到我們的測試環境中。我們還原完資料庫之後，執行了以下 T-SQL 命令，這是一個預存程序 (Stored Procedure)：
EXEC usp_BatchJobs '2023/11/30', '1', '1'
執行的過程出現了以下錯誤：
Msg 468, Level 16, State 9, Procedure usp_BatchJobs, Line 199 [Batch Start Line 0]
Cannot resolve the collation conflict between "Chinese_Taiwan_Stroke_CS_AS" and "Chinese_Taiwan_Stroke_CI_AS" in the equal to operation.
從錯誤訊息中，我們可以看到是因為兩個不同的定序 (Chinese_Taiwan_Stroke_CS_AS 與 Chinese_Taiwan_Stroke_CI_AS) 在比較時發生了衝突。這是因為 SQL Server 在比較字串時，會根據資料庫的定序來進行比較，如果兩個不同定序的資料庫進行比較時，就會出現這個錯誤。
追查問題：釐清定序的觀念
在 Microsoft SQL Server 中，定序 (Collation) 決定了資料的排序和比較規則，所以「定序」也可以翻譯為「排序規則」。定序名稱通常由語言、排序規則和大小寫敏感組成，例如本次問題遇到衝突的定序 Chinese_Taiwan_Stroke_CS_AS 和 Chinese_Taiwan_Stroke_CI_AS 可以這樣理解：
Chinese_Taiwan: 表示使用繁體 中文(台灣) 的語言設定Stroke: 指以筆劃順序進行排序CS: 區分大小寫 (Case Sensitive)CI: 不區分大小寫 (Case Insensitive)AS: 區分重音符號 (Accent Sensitive)
主要差異：
應用情境：
選擇適當的定序設定，能確保資料庫操作符合應用程式的需求，避免因大小寫或重音符號處理不當而導致的問題。
我們在本機安裝的 SQL Server 預設定序是 Chinese_Taiwan_Stroke_CI_AS，而客戶提供的資料庫定序是 Chinese_Taiwan_Stroke_CS_AS，這就是為什麼我們在執行客戶提供的預存程序時，會發生定序衝突的問題。
追查問題：理解定序的層級
在理解定序的用途後，再來看看有哪些地方可以定義定序。在 SQL Server 中，定序可以存在於不同的層級，包括：
-
伺服器層級
SQL Server 安裝時會設定一個預設的定序，這會成為伺服器的預設定序。此定序通常是伺服器層級的預設規則，用來處理系統字串和系統級的操作。
查詢伺服器預設定序的 T-SQL 命令如下：
SELECT CONVERT (varchar(256), SERVERPROPERTY('collation'));
-
資料庫層級
每個資料庫都有一個預設的排序規則 (預設定序)，這是資料庫中所有字串欄位的預設排序規則。當你建立資料表、檢視表或索引時，如果不指定排序規則，預設就會使用資料庫的預設定序。
你可以透過以下 T-SQL 命令快速查出資料庫的預設定序：
USE master;
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation');
USE RMPDB_TRE;
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation');
當然，這個資料庫預設定序是可以修改的，但僅限於「使用者資料庫」可以直接改，T-SQL 命令如下：
ALTER DATABASE YourDatabaseName COLLATE Chinese_Taiwan_Stroke_CI_AS;
若要調整「系統資料庫」會比較麻煩，本文稍後會提供解決方法。
-
資料表層級
由於定序主要是針對字串欄位的排序規則，因此並沒有在資料表層級指定定序的設定，主要是針對「資料表中的字串欄位」才有定序的設定。
每個資料表的字串欄位可以有自己的定序，這些定序會覆蓋資料庫層級的預設定序。當資料表建立時，如果沒有明確指定欄位的定序，則會使用資料庫的預設定序。
你可以透過以下 T-SQL 命令快速查出目前資料庫中所有表格的字串欄位所指定的定序：
SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
若要更改欄位的排序規則，可以參考以下 T-SQL 命令：
ALTER TABLE YourTableName
ALTER COLUMN YourColumnName VARCHAR(50) COLLATE Chinese_Taiwan_Stroke_CI_AS;
-
查詢層級
當進行字串比較或 JOIN 操作時，可以在查詢中使用 COLLATE 子句來指定特定的排序規則。這是在查詢層級解決排序規則衝突的一種方式，尤其是在進行 JOIN 操作時，兩個不同排序規則的欄位之間進行比較時。
在 T-SQL 查詢中使用 COLLATE 的範例如下：
SELECT *
FROM Table1 t1
JOIN Table2 t2
ON t1.Column COLLATE Chinese_Taiwan_Stroke_CI_AS = t2.Column COLLATE Chinese_Taiwan_Stroke_CI_AS;
我們也可以針對字串常數進行定序的宣告。在查詢中，字串常數 (例如 'some text' ) 也會有排序規則。若資料表欄位的排序規則與字串常數的排序規則不一致，則可能會導致排序規則衝突。此時，可以使用 COLLATE 子句來強制將字串常數轉換為指定的排序規則。範例如下：
SELECT *
FROM YourTable
WHERE YourColumn = 'some text' COLLATE Chinese_Taiwan_Stroke_CI_AS;
-
索引層級
在建立索引時，索引欄位的定序也會影響索引的比較行為。若欄位的定序和索引使用的定序不同，則可能會出現問題或性能上的差異。
建立索引時指定定序的範例如下：
CREATE INDEX idx_YourIndex
ON YourTable (YourColumn COLLATE Chinese_Taiwan_Stroke_CI_AS);
瞭解這些不同層級的定序設定，可以幫助你更好地管理排序規則，避免排序衝突，並提高查詢的準確性和效率。
追查問題：瞭解定序衝突的來源
在進一步解決問題之前，我們需要瞭解定序衝突的來源。在這個案例中，我們遇到的問題是因為客戶提供的資料庫和我們本地測試環境的資料庫使用了不同的排序規則。
我先從客戶提供的預存程序中找到出錯的地方，先執行以下 T-SQL 命令：
EXEC usp_BatchJobs '2023/11/30', '1', '1'
發現錯誤後，去追查這份 usp_BatchJobs 預存程序包含了好幾個暫存資料表的建立：
--1.0 建立暫存表
Create Table #TempA (
ReportSeqNo INT
,FIN_TYPE VARCHAR(50)
,FIN_TYPE2 VARCHAR(50)
,ColA VARCHAR(20)
)
不但會將資料寫入暫存資料表，還會拿這份暫存資料表與現有資料表進行操作：
INSERT INTO #TempA
SELECT ReportSeqNo,FIN_TYPE,FIN_TYPE2,0
FROM dbo.fun_ReportItemDate('8A',@P_DATE) AS m
這裡的問題就在於，當我們嘗試寫入暫存資料表時，實際上這些資料都會被寫入到 TempDB 這個系統資料庫中。而 TempDB 資料庫的排序規則是由系統資料庫的排序規則所決定的。因次，當我們伺服器層級的定序為 Chinese_Taiwan_Stroke_CI_AS，而 TempDB 的定序預設就會是 Chinese_Taiwan_Stroke_CI_AS 時，然而我們客戶的資料庫預設定序為 Chinese_Taiwan_Stroke_CS_AS，且還原的資料庫中所有的表格中的字串欄位都是 Chinese_Taiwan_Stroke_CS_AS 定序。這樣一來，當我們在 TempDB 中建立暫存資料表時，就會使用 Chinese_Taiwan_Stroke_CI_AS 的定序，而當我們從客戶的資料庫中取得資料時，就會使用 Chinese_Taiwan_Stroke_CS_AS 的定序，這樣一來就會導致定序衝突，這才是本次問題的發生主因！
解決方案
在瞭解了整個來龍去脈後，我們可以採取以下解決方案：
-
設法調整所有預存程序的定序使用，讓這些暫存資料表明確使用 Chinese_Taiwan_Stroke_CS_AS 的定序。
這是一個比較直接的解決方案，但需要修改所有的預存程序，並確保所有的暫存資料表都使用相同的定序。這樣可以避免定序衝突，但需要花費一些時間和精力。由於客戶提供的資料庫中有多達 1,500 個預存程序，一個一個改真的太累了，所以我們這次放棄了這個作法。
-
設法調整 TempDB 的定序，使其與客戶的資料庫定序一致。
調整 TempDB 的定序是一個比較複雜的操作，首先，他是一個系統資料庫，不能夠讓資料庫還在線上運作時調整。簡單的作法當然是直接重新安裝 SQL Server，並且在安裝時進行定序的設定。另一種方法也是我這篇文章要分享的，透過幾個簡單的命令，不用重灌也可以快速切換系統資料庫的定序。
以下就是在不用重新安裝 SQL Server 的情況下切換系統資料庫定序的方式：
-
停止 SQL Server 服務
如果你本機是安裝 SQLEXPRESS 的話，那麼停用服務的命令是這樣：
net stop MSSQL$SQLEXPRESS
-
使用單一使用者模式啟動 SQL Server 並指定伺服器層級定序
cd "C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Binn"
sqlservr -m -T4022 -T3659 -s"SQLEXPRESS" -q "Chinese_Taiwan_Stroke_CS_AS"
-
sqlservr：
這是 SQL Server 的執行檔，用來啟動 SQL Server 實例。
-
-m：
啟動 SQL Server 實例時，以「單一使用者模式」運行。這意味著只有一個連線可以進入 SQL Server，通常用於維護或排除故障的情況。
-
-T4022：
這個追蹤標誌允許 SQL Server 即使遇到某些系統錯誤也能繼續啟動。它強制 SQL Server 在發生輕微錯誤時仍然繼續啟動。
-
-T3659：
這個追蹤標誌關閉某些安全檢查，使 SQL Server 在啟動時忽略某些安全錯誤或問題。這是用來排除故障時有用，但需要小心使用，因為它會繞過安全檢查。
-
-s"SQLEXPRESS"：
這個選項指定要啟動的 SQL Server 實例名稱。SQLEXPRESS 是 SQL Server Express 版本的預設實例名稱。若使用不同的實例名稱，需要將其替換為對應的名稱。
-
-q "Chinese_Taiwan_Stroke_CS_AS"：
這個選項設定 SQL Server 實例的定序 (collation)。Chinese_Taiwan_Stroke_CS_AS 是指定使用「繁體中文（台灣）」的字元集和排序規則，按照台灣的筆劃順序來進行排序，並且是區分大小寫（CS）及區分重音符號（AS）。
執行完畢你會看到以下訊息：
The default collation was successfully changed.
Recovery is complete. This is an informational message only. No user action is required.
然後就可以按下 Ctrl+C 並輸入 Y 來結束 SQL Server 的執行。
-
重新啟動 SQL Server 即可
net start MSSQL$SQLEXPRESS
總結
變更伺服器定序是一個高風險的操作，因為切換伺服器定序的過程中，預設會將所有系統資料庫重建，所有使用者資料庫的索引也會自動重建。如果資料庫中的定序無法與系統資料庫相容，就會引發新的問題。建議在本機實驗就好，否則很有可能會造成無法預期的問題。
今天這篇文章，如果只是要答案，就以下三個命令而已（但風險自負喔）：
net stop MSSQL$SQLEXPRESS
"C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Binn\sqlservr" -m -T4022 -T3659 -s"SQLEXPRESS" -q "Chinese_Taiwan_Stroke_CS_AS"
net start MSSQL$SQLEXPRESS
最後幾點提醒：
- 切換伺服器定序之前，建議先卸載(
Detach)所有的使用者資料庫(User databases)
- 切換伺服器定序之後，再將需要的資料庫重新附加(
Attach)回來
相關連結