五餅二魚工作室

這一陣子都忙著寫程式和弄  Azure ，因此都比較沒有時間碰 SQL Server ，剛好前幾天看到一個有趣的題目，因此就把相關的討論給整理一下囉。

一開始網友提出的題目是這樣的：

伺服器定序廠商原本安裝Chinese_Taiwan_Stroke_CI_AS，只有一個User DB，經過一段時間測試之後，資料量約有5GB，看起來只有用到table，也沒其他procedure或sql job。
但現在被要求定序要改成CS_AS 是執行了如下步驟rebuild master + restore user DB，開起來後檢查及使用看起來都還正常。

1.backup all DB(A)
2.change user DB [AAA] collation to CS_AS from CI_AS
3.backup all DB(B) again
4.rebuild master DB
cd C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014
setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS= ABC\sql /SAPWD=*** /SQLCOLLATION=Chinese_Taiwan_Stroke_CS_AS
5.restore user DB [AAA](使用(B)的bak檔)
6.重開機

從表面上看起來似乎都沒有錯，但實際上如果按照上述的做法，雖然把資料庫的 Instance 的定序給改變了，也把要變更定序的資料庫也改變了。但實際上來說，上述的方式對於原本的問題一點幫助都沒有，因為原本已經建立好的資料表，他的文字型態的欄位 ( char , varchar , nchar , nvarchar )，還是原本建立資料表時候的定序，因此才會說做了上述的步驟之後，一點幫助都沒有。

---

下面我們就整個來做個 Lab ，展示相關的處理，這裡我就先跳過更改 Instance 定序的方式，有需要的朋友可以參考另外一篇文章 ( [SQL][Azure]調整 Azure SQL VM 的資料庫定序 ) 的說明。因此下面的範例我就先建立一個測試資料庫，也模擬放入一些資料。

CREATE DATABASE [DEMO] COLLATE Chinese_Taiwan_Stroke_CI_AI
GO

USE [DEMO]
GO

CREATE TABLE T1( 
	F1	VARCHAR(10)	PRIMARY KEY,
	F2	CHAR(10),
	F3	NVARCHAR(10),
	F4	NCHAR(10)
)
GO

INSERT INTO T1(F1,F2,F3,F4) VALUES
	( 'A', 'A', 'A', 'A'),
	( 'B', 'B', 'B', 'B'),
	( 'C', 'C', 'C', 'C'),
	( 'D', 'D', 'D', 'D')
GO

 

因為這個時候資料庫設定是不分大小寫的，所以我們要注意一點，在 SQL Server 內，定序不單純只影響資料本身的排序，也會影響使用這些物件名稱，因此在資料庫設定部分大小寫的狀況下，我們用下面語法都可以順利取得資料

USE [DEMO]
GO

-- 使用大寫
SELECT * FROM T1

-- 使用小寫
SELECT * FROM t1
GO

此時我們要來改變這個範例資料庫的定序了，我個人比較偏好這個部分用指令來做，當然您也可以用  GUI 的操作來做，但可能就比較容易被一些開啟的連線影響到無法順利執行。

USE [master]
GO

-- 設定為單一使用者
ALTER DATABASE [DEMO] SET SINGLE_USER;
GO

-- 變更資料庫定序
ALTER DATABASE [DEMO] COLLATE Chinese_Taiwan_Stroke_CS_AS
GO

-- 恢復成為多人使用
ALTER DATABASE [DEMO] SET MULTI_USER;
GO

 

完成之後，我們可以看到資料庫定序果然如我們所想要的變更了。

但此時我們要是注意去看資料表內的文字型態欄位，會發現他的定序還是維持在原本建立時候的定序

當然這個時候可以用語法去改變欄位的定序

USE [DEMO]
GO

ALTER TABLE dbo.T1 ALTER COLUMN F1 varchar(10) COLLATE Chinese_Taiwan_Stroke_CS_AS;  
GO  

ALTER TABLE dbo.T1 ALTER COLUMN F2 char(10) COLLATE Chinese_Taiwan_Stroke_CS_AS;  
GO  

ALTER TABLE dbo.T1 ALTER COLUMN F3 nvarchar(10) COLLATE Chinese_Taiwan_Stroke_CS_AS;  
GO  

ALTER TABLE dbo.T1 ALTER COLUMN F4 nchar(10) COLLATE Chinese_Taiwan_Stroke_CS_AS;  
GO  

 

但會發現是沒有辦法正常執行的

這個部分可以參考 MSDN 上的說明

如果目前下列任何一個項目參考資料行定序的話，就無法變更其定序：
●計算資料行
●索引
●散發統計資料，不論是自動產生或由 CREATE STATISTICS 陳述式產生
●CHECK 條件約束
●FOREIGN KEY 條件約束

 

按照文件上的說明，看起來不只要欄位一個一個去設定之外，還要去除上述的狀況之後，才有辦法來變更欄位的定序，這樣弄起來就麻煩多了。因此這裡我們可以換個方式來做，利用重新建立的方式來做處理。首先我們可以在 SSMS 上面選擇原本的資料庫，按下滑鼠右鍵選擇「工作」→「產生指令碼」

在設定指令碼編選選項的時候，記得選擇「進階」

要注意記得將「指令碼定序」給設定為「False」

將產出的指令碼做些修改，讓指令去建立的一個新的資料庫的時候要改用不同的名稱，並且指令你所想要的定序，在這裡我直接將原本建立 DEMO 資料庫的指令改成如下的方式，然後就可以放下去執行，產生新的資料庫和相關物件了。

USE [master]
GO

CREATE DATABASE [DEMO_NEW] COLLATE Chinese_Taiwan_Stroke_CS_AS 
GO

 

建立好之後，目前資料庫是空的，因此我這裡使用 SSMS 工具上的匯出匯入資料庫的功能來處理資料

選擇好來源和目的的資料庫，就可以順利完成了

轉換好之後，就可以用指令把兩個資料庫名稱給對調了

ALTER DATABASE DEMO SET SINGLE_USER WITH NO_WAIT
GO

ALTER DATABASE DEMO_NEW SET SINGLE_USER WITH NO_WAIT
GO

EXEC sp_renamedb 'DEMO' , 'DEMO_OLD' ;
GO

EXEC sp_renamedb 'DEMO_NEW' , 'DEMO' ;
GO

ALTER DATABASE DEMO_OLD SET MULTI_USER WITH NO_WAIT
GO

ALTER DATABASE DEMO SET MULTI_USER WITH NO_WAIT
GO

這樣就完成一個簡單的任務了。

---

從上面的 Lab 做下來，看起來都是蠻簡單的，但這裡可能有幾個地方還是要去注意一下：

1. 原本在寫 SQL 指令的時候可以不用區分物件的大小寫，那現在定序改變之後就要注意了，這樣會不會使得原本的程式造成異常呢 ?

2. 原本的資料是不分大小寫，因此資料內不會同時存有 'A' 和 'a' 這樣的狀況，但轉換之後因為區分大小寫，使得資料在判斷上  'A' 和 'a' 就會視為不同，這樣原本程式內的商業邏輯是否會影響到，也是要去注意的。

3.眼尖的人應該會發現我們在使用產生指令碼的時候，如果把預設方式改為「結構描述和資料」，那樣不是就可以不用再做匯入了嗎 ? 基本上來說是的，只是如果資料庫資料較少的時候，還可以那樣做，但資料筆數要是很多的時候，我個人還是建議分兩段來處理，會比較合適一點。

4. 基本上如果很單純使用的狀況下，其實是可以不用改 Instance 定序，只要更改資料庫自己和資料庫內物件的定序就可以了，但改成全部一樣是可以避免一些問題，至於是那些問題，那就留待下一篇來說明囉。