- 取得連結
- X
- 以電子郵件傳送
- 其他應用程式
本文章將介紹變更欄位資料型態的另一種作法(請評估實際情境是否適合此方式)
建立環境與實作指令碼可以在下面 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)
後紀
要使用直接修改還是使用新建欄位方式可依照資料表的特性選擇
資料量不大的話直接變更資料型態反而會比較適合,或者是應用程式不提供運作的時間
也可以依需求變更新舊欄位間資料更新的方式
留言
張貼留言