# 如何變更 SQL Server 的系統資料庫定序以避免暫存資料庫的定序衝突

我們上周公司啟動了一個新專案，我們工程師拿到客戶的資料庫之後，發現客戶提供的預存程序無法順利執行，發生了資料庫定序 (collation) 的衝突問題。我們負責的工程師由於是第一次遇到這個狀況，所以鬼打牆了一段時間，因為我上周都在備課，所以我跟他講：「你直接移除重裝 SQL Server 並明確指定定序會比較快，我現在沒時間跟你講太多技術細節。」今天這篇文章我打算來說說這個問題背後的技術細節。

![visualizing the complexities of SQL Server collation conflicts and resolutions](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/f5e1e4fc885ad0186147-df120e3b-01c8-407c-917b-7df0670949ab.webp)

### 問題描述

首先，我們先拿到客戶提供的資料庫備份檔 (`*.bak`)，並且還原到我們的測試環境中。我們還原完資料庫之後，執行了以下 T-SQL 命令，這是一個預存程序 (Stored Procedure)：

```sql
EXEC usp_BatchJobs '2023/11/30', '1', '1'
```

執行的過程出現了以下錯誤：

```sql
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)

主要差異：

-   `Chinese_Taiwan_Stroke_CS_AS`：區分大小寫和重音符號。在此定序下，`'A'` 與 `'a'` 被視為不同的字元，`'a'` 與 `'á'` 也被視為不同。
    
-   `Chinese_Taiwan_Stroke_CI_AS`：不區分大小寫，但區分重音符號。在此定序下，`'A'` 與 `'a'` 被視為相同，但 `'a'` 與 `'á'` 被視為不同。
    

應用情境：

-   `Chinese_Taiwan_Stroke_CS_AS`：適用於需要精確區分大小寫的應用程式，例如密碼驗證系統。
    
-   `Chinese_Taiwan_Stroke_CI_AS`：適用於不需要區分大小寫的應用程式，例如一般的文字搜尋功能。
    

選擇適當的定序設定，能確保資料庫操作符合應用程式的需求，避免因大小寫或重音符號處理不當而導致的問題。

我們在本機安裝的 SQL Server 預設定序是 `Chinese_Taiwan_Stroke_CI_AS`，而客戶提供的資料庫定序是 `Chinese_Taiwan_Stroke_CS_AS`，這就是為什麼我們在執行客戶提供的預存程序時，會發生定序衝突的問題。

### 追查問題：理解定序的層級

在理解定序的用途後，再來看看有哪些地方可以定義定序。在 SQL Server 中，定序可以存在於不同的層級，包括：

1.  **伺服器層級**
    
    SQL Server 安裝時會設定一個預設的定序，這會成為伺服器的預設定序。此定序通常是伺服器層級的預設規則，用來處理系統字串和系統級的操作。
    
    查詢伺服器預設定序的 T-SQL 命令如下：
    
    ```sql
    SELECT CONVERT (varchar(256), SERVERPROPERTY('collation'));
    ```
    
2.  **資料庫層級**
    
    每個資料庫都有一個**預設的排序規則** (**預設定序**)，這是資料庫中所有**字串欄位**的預設排序規則。當你建立**資料表**、**檢視表**或**索引**時，如果不指定排序規則，預設就會使用**資料庫**的**預設定序**。
    
    你可以透過以下 T-SQL 命令快速查出資料庫的預設定序：
    
    ```sql
    USE master;
    SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation');
    
    USE RMPDB_TRE;
    SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation');
    ```
    
    當然，這個資料庫預設定序是可以修改的，但僅限於「使用者資料庫」可以直接改，T-SQL 命令如下：
    
    ```sql
    ALTER DATABASE YourDatabaseName COLLATE Chinese_Taiwan_Stroke_CI_AS;
    ```
    
    若要調整「系統資料庫」會比較麻煩，本文稍後會提供解決方法。
    
3.  **資料表層級**
    
    由於**定序**主要是針對**字串欄位**的排序規則，因此**並沒有**在**資料表**層級指定定序的設定，主要是針對「**資料表中的字串欄位**」才有**定序**的設定。
    
    每個資料表的**字串欄位**可以有自己的**定序**，這些**定序**會覆蓋**資料庫層級**的**預設定序**。當資料表建立時，如果沒有明確指定欄位的定序，則會使用**資料庫的預設定序**。
    
    你可以透過以下 T-SQL 命令快速查出**目前資料庫**中**所有表格**的**字串欄位**所指定的**定序**：
    
    ```sql
    SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    ```
    
    若要更改欄位的排序規則，可以參考以下 T-SQL 命令：
    
    ```sql
    ALTER TABLE YourTableName
    ALTER COLUMN YourColumnName VARCHAR(50) COLLATE Chinese_Taiwan_Stroke_CI_AS;
    ```
    
4.  **查詢層級**
    
    當進行**字串比較**或 `JOIN` 操作時，可以在查詢中使用 `COLLATE` 子句來指定特定的排序規則。這是在查詢層級解決排序規則衝突的一種方式，尤其是在進行 JOIN 操作時，兩個不同排序規則的欄位之間進行比較時。
    
    在 T-SQL 查詢中使用 `COLLATE` 的範例如下：
    
    ```sql
    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` 子句來強制將字串常數轉換為指定的排序規則。範例如下：
    
    ```sql
    SELECT *
    FROM YourTable
    WHERE YourColumn = 'some text' COLLATE Chinese_Taiwan_Stroke_CI_AS;
    ```
    
5.  **索引層級**
    
    在建立索引時，索引欄位的定序也會影響索引的比較行為。若欄位的定序和索引使用的定序不同，則可能會出現問題或性能上的差異。
    
    建立索引時指定定序的範例如下：
    
    ```sql
    CREATE INDEX idx_YourIndex
    ON YourTable (YourColumn COLLATE Chinese_Taiwan_Stroke_CI_AS);
    ```
    

瞭解這些不同層級的定序設定，可以幫助你更好地管理排序規則，避免排序衝突，並提高查詢的準確性和效率。

### 追查問題：瞭解定序衝突的來源

在進一步解決問題之前，我們需要瞭解定序衝突的來源。在這個案例中，我們遇到的問題是因為客戶提供的資料庫和我們本地測試環境的資料庫使用了不同的排序規則。

我先從客戶提供的預存程序中找到出錯的地方，先執行以下 T-SQL 命令：

```sql
EXEC usp_BatchJobs '2023/11/30', '1', '1'
```

發現錯誤後，去追查這份 `usp_BatchJobs` 預存程序包含了好幾個**暫存資料表**的建立：

```sql
--1.0 建立暫存表
Create Table #TempA (
  ReportSeqNo INT
  ,FIN_TYPE   VARCHAR(50)
  ,FIN_TYPE2  VARCHAR(50)
  ,ColA       VARCHAR(20)
)
```

不但會將資料寫入暫存資料表，還會拿這份暫存資料表與現有資料表進行操作：

```sql
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` 的定序，這樣一來就會導致定序衝突，這才是本次問題的發生主因！

### 解決方案

在瞭解了整個來龍去脈後，我們可以採取以下解決方案：

1.  設法調整所有預存程序的定序使用，讓這些暫存資料表明確使用 `Chinese_Taiwan_Stroke_CS_AS` 的定序。
    
    這是一個比較直接的解決方案，但需要修改所有的預存程序，並確保所有的暫存資料表都使用相同的定序。這樣可以避免定序衝突，但需要花費一些時間和精力。由於客戶提供的資料庫中有多達 1,500 個預存程序，一個一個改真的太累了，所以我們這次放棄了這個作法。
    
2.  設法調整 `TempDB` 的定序，使其與客戶的資料庫定序一致。
    
    調整 `TempDB` 的定序是一個比較複雜的操作，首先，他是一個系統資料庫，不能夠讓資料庫還在線上運作時調整。簡單的作法當然是直接重新安裝 SQL Server，並且在安裝時進行定序的設定。另一種方法也是我這篇文章要分享的，透過幾個簡單的命令，不用重灌也可以快速切換系統資料庫的定序。
    

以下就是在不用重新安裝 SQL Server 的情況下切換系統資料庫定序的方式：

1.  停止 SQL Server 服務
    
    如果你本機是安裝 `SQLEXPRESS` 的話，那麼停用服務的命令是這樣：
    
    ```bash
    net stop MSSQL$SQLEXPRESS
    ```
    
2.  使用**單一使用者模式**啟動 SQL Server 並指定伺服器層級定序
    
    ```batch
    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 的執行。
    
3.  重新啟動 SQL Server 即可
    
    ```bash
    net start MSSQL$SQLEXPRESS
    ```
    

### 總結

變更伺服器定序是一個高風險的操作，因為切換伺服器定序的過程中，預設會將所有系統資料庫重建，所有使用者資料庫的索引也會自動重建。如果資料庫中的定序無法與系統資料庫相容，就會引發新的問題。建議在本機實驗就好，否則很有可能會造成無法預期的問題。

今天這篇文章，如果只是要答案，就以下三個命令而已（但風險自負喔）：

```batch
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
```

最後幾點提醒：

1.  切換**伺服器定序**之前，建議先**卸載**(`Detach`)所有的**使用者資料庫**(User databases)
2.  切換**伺服器定序**之後，再將需要的資料庫重新**附加**(`Attach`)回來

### 相關連結

-   [如何修復 Microsoft SQL Server 毀損的 model 資料庫](/post/2023/01/01/How-to-fix-Microsoft-SQL-Server-broken-model-system-database)