前言:什麼是定序?
定序是一種資料庫的屬性,用來指定資料庫中字串的排序和比較規則。
例如:
- Chinese_Taiwan_Stroke_CI_AS
- Chinese_Taiwan_Stroke_90_CI_AS_SC
Stroke:用筆畫進行比對或排序
Bopomofo:用ㄅㄆㄇㄈ進行比對或排序。
CI(Case InSensitive):不區分大小寫
AS(Accent Sensitive):區分音調(如有注音符號的字母不同會視為不同)
SC:支援增補字集相關的定序(如 CJK 擴展字元)
90:表示此定序沿用 SQL Server 2005(9.0)版本的排序規則
SC定序是 SQL Server 2012 (11.x)新引進的與增補字集有關的定序。從 SQL Server 2017 (14.x) 開始,所有新的定序都會自動支援增補字元。
有些定序會加上數字
用以區別是哪一版本的資料庫開始支援,例如含有90的定序,就是SQL Server 2005(9.0)版本的排序規則。
查詢物件的定序
--取得 SERVER 的預設定序
SELECT CONVERT (varchar, SERVERPROPERTY('collation'))
--取得 DATABASE 的定序
SELECT CONVERT (varchar, DATABASEPROPERTYEX('YFEP','collation'))
--取得 TABLE 中每個欄位的定序
SELECT
t.name, c.name, c.collation_name
FROM sys.columns c
INNER JOIN sys.tables t ON t.object_id = c.object_id
WHERE c.object_id
IN (SELECT object_id FROM sys.objects WHERE type = 'U')
AND c.collation_name != 'NULL'
And t.name='UserMake'
ORDER BY t.name, c.name
如何變更定序
定序設定可分成伺服器層級、資料庫層級、資料行層級。 雖然每個層級的定序都可以變更,但對原本已經建立好的資料,變更上層層級的定序,並不會改變原先下層層級的定序。
1. 變更資料庫的定序
-- 設定為單一使用者
ALTER DATABASE [SchoolDB] SET SINGLE_USER;
GO
-- 變更資料庫定序
ALTER DATABASE [SchoolDB] COLLATE Chinese_Taiwan_Stroke_CI_AS
GO
-- 恢復成為多人使用
ALTER DATABASE [SchoolDB] SET MULTI_USER;
GO
2. 變更資料行(欄位)的定序
ALTER TABLE Student ALTER COLUMN FirstName nvarchar(50) COLLATE Chinese_Taiwan_Stroke_CI_AS
雖然欄位的定序可以變更,但 MSDN 上有寫,如果欄位參考了下列任何一個項目的定序,就無法變更其定序:
- 計算資料行
- 索引
- 散發統計資料,不論是自動產生或由 CREATE STATISTICS 陳述式產生
- CHECK 條件約束
- FOREIGN KEY 條件約束
如何改變整個資料庫的定序
一個已經運行中的資料庫,變更資料庫的定序,並不會同時更改所有資料欄位的定序。 而更改資料欄位的定序,又很容易遇到以上不可變更的狀況。
若要變更整個資料庫定序,可參考以下做法:以新定序建立一個新的資料庫,並將原始資料庫的資料複製到新的資料庫。
1. 由原始資料庫,產出資料庫建立的完整腳本
使用 SSMS 建立整個資料庫的完整腳本,但不含資料,這樣就可以得到一個包含所有資料表、索引、條件約束、預存程序、檢視等等的腳本。這個腳本可以用來建立一個和原始資料庫結構一樣的新資料庫。
在設定 SSMS 建立腳本的步驟中,進階選項內有幾個選項要注意:
General
Script Collaton:要設定為 False (預設值就為 Fase)
設成 False,就不會在腳本中產生定序的相關設定,在底下步驟中,我們會微調腳本內容,以便建立新的資料庫時,使用我們要的資料庫定序。Table/View Optionsl
這裡面的選項,可依實際狀況進行調整,例:
- Script Full-Text Indexes:預設值為 Fase,若有用到就改為 True。
- Script Trigger:預設值就為 Fase,若有用到就改為 True。
- Script Indexes:預設值就為 True,應該都有用到,就用預設值。
2. 使用腳本建立新的資料庫
將上一個步驟中產出的資料庫腳本,依實際需要進行修改,例如:
- 修改
資料庫名稱
及資料庫的檔案名稱及位置
- 指定
資料庫定序
因為前面在建立腳本時,我們沒有輸出定序資訊,所以此時將要使用的定序加進腳本中。修改前後對照如下:
調整完之後,執行腳本,就可以得到一個資料庫結構和原始資料庫一樣的,但定序是我們要的新定序。
3. 使用 Export/Import 功能,將原始資料庫中的資料複製到新的資料庫。
接下來在 SSMS 中使用「匯出/匯入」功能,將舊有資料庫的資料,全部匯入到新的資料庫,不過這個操作,通常會遇到幾個問題:
- FOREIGN KEY 條件約束(CONSTRAINT)
- Identity 欄位值無法匯入
這二個問題,可以透過下列方式解決:
停用全部資料庫內的 FOREIGN KEY 條件約束
下面語法,可以停用/啟用資料庫內的 FOREIGN KEY 條件約束(CONSTRAINT)、CHECK 條件約束(CONSTRAINT)
-- 全部停用:資料庫內的 FOREIGN KEY 條件約束(CONSTRAINT)、CHECK 條件約束(CONSTRAINT)
USE YFEP_New
GO
EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"
GO
-- 全部啟用:資料庫內的 FOREIGN KEY 條件約束(CONSTRAINT)、CHECK 條件約束(CONSTRAINT)
-- 不檢查現有資料
USE YFEP_New
GO
EXEC sp_MSforeachtable @command1="ALTER TABLE ? WITH NOCHECK CHECK CONSTRAINT ALL"
GO
啟用識別插入
在匯出/匯入的編輯對應
功能中,可以選擇「啟用識別插入」(Enable identity insert),這樣在匯入資料時,就可以保留原始資料庫中的識別值。
若有多個資料表要設,可以全部選取,一起設定啟用識別插入。
經由上面的設定,若沒其他情況,「匯出/匯入」作業應該就可以順利完成,此時就得到一個內容及結構都和原先資料庫一樣,但定序是新的定序。
匯出/匯入作業完成之後,記得將原始資料庫改名,並將新的資料庫改回原始資料庫的名稱,同時記得再啟用 FOREIGN KEY 條件約束。
ALTER DATABASE YFEP SET SINGLE_USER WITH NO_WAIT
GO
EXEC sp_renamedb 'YFEP' , 'YFEP_OLD' ;
GO
ALTER DATABASE YFEP_OLD SET MULTI_USER WITH NO_WAIT
GO
ALTER DATABASE YFEP_NEW SET SINGLE_USER WITH NO_WAIT
GO
EXEC sp_renamedb 'YFEP_NEW' , 'YFEP' ;
GO
GO
ALTER DATABASE YFEP SET MULTI_USER WITH NO_WAIT
GO
以上就是如何變更整個資料庫的定序。 另外,匯出/匯入作業會增加大量的交易記錄,所以在執行匯出/匯入後,可使用 SHRINKFILE LOG 壓縮目前資料庫的資料或記錄檔大小。