軟體工程師的小事：到底要不要 Close

0

使用新增欄位與重新命名方式變更資料表欄位型態

本文章將介紹變更欄位資料型態的另一種作法（請評估實際情境是否適合此方式）

建立環境與實作指令碼可以在下面 Github 中取得

txstudio/alter-column-type-use-rename-column

建置環境的步驟請參考 Github 的 readme.md 說明

因可能會有修正，詳細的指令碼請以 github 提供內容為主

設定好範例資料庫與資料表內容後，dbo.Stock 資料表共有 90 萬筆資料

其中 Stock 資料表中的 Name 欄位型態為 NVARCHAR(MAX)，應給予該欄位正確的字串長度

要調整該資料欄位型態為 NVARCHAR(50)

使用 ATLER COLUMN 變更資料型態

請參考下列指令碼
alter-column-type-use-rename-and-goto.sql

使用 ALTER TABLE ... ALTER COLUMN 進行欄位型態更新

ALTER TABLE [dbo].[Stock]
	ALTER COLUMN [Name] NVARCHAR(50)
GO

更新時間為 47 秒，在這 47 秒的過程中資料表是被鎖定的

可能就會影響到應用程式對該資料表的存取

使用新增欄位的方式進行欄位更新

請參考下列指令碼
alter-column-type-use-rename-and-goto.sql

範例指令碼將使用 sp_rename, GOTO 協助將舊欄位資料更新到新的欄位

先建立資料型態正確的欄位

ALTER TABLE [dbo].[Stock]
	ADD [Name_New] NVARCHAR(50)
GO

使用 sp_rename 將舊欄位與新欄位名稱互換

EXECUTE sp_rename N'dbo.Stock.Name',N'Name_Old','Column'
EXECUTE sp_rename N'dbo.Stock.Name_New',N'Name','Column'
GO

使用 LABEL 與 GOTO 指令將較新的資料先更新到新的欄位

UPDATE_MOVE:

--逐步更新資料欄位型態
;WITH UPDATE_BASE AS (
	SELECT TOP(5000) [Name]
		,[Name_Old]
	FROM [dbo].[Stock]
	WHERE [Name_Old] IS NOT NULL
	ORDER BY [No] DESC
)
UPDATE UPDATE_BASE
	SET [Name] = [Name_Old]
		,[Name_Old] = NULL
WHERE [Name_Old] IS NOT NULL

IF @@ROWCOUNT > 0
	GOTO UPDATE_MOVE
GO

相較直接修改欄位時間需要 02:16 ，但資料表是在可存取的狀態

更新完成後就將資料型態錯誤的欄位進行刪除

ALTER TABLE [dbo].[Stock]
	DROP COLUMN [Name_Old] 
GO

資料型態開的不好的欄位已經被刪除

原本 Name 資料型態為 NVARCHAR(MAX) 被更新為 NVARCHAR(50)

後紀

要使用直接修改還是使用新建欄位方式可依照資料表的特性選擇

資料量不大的話直接變更資料型態反而會比較適合，或者是應用程式不提供運作的時間

也可以依需求變更新舊欄位間資料更新的方式

參考資料

留言

張貼留言