[SQL] 避免在 SSMS 圖形介面上修改資料型態

同事告知更新自己電腦上的 SQL Server 欄位資料型態時，常常會出現 Timeout 的情況，建議更新欄位資料型態，千萬不要利用 SSMS 去做，一定要利用 T-SQL 語法進行

• 透過 SSMS 更新欄位資料型態

建立一個 Table 來觀察 SSMS 如何更改欄位資料型態

USE AdventureWorks2012
GO

CREATE TABLE DataTypeModify 
(
    Data datetime
)

利用 SSMS 把 Data 欄位資料型態從 samlldatetime 轉為 date

點選資料表設計工具（檢視 => 工具列 => 資料表設計工具）內的產生變更指令碼

SSMS 變更欄位資料型態指令碼，從 T-SQL 就可以發現 SSMS 其實沒有很聰明地變更欄位資料型態

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO

-- 1.Tmp_DataTypeModify 為 SQL Server 自建的 Temp Table
CREATE TABLE dbo.Tmp_DataTypeModify
(
     Data date NULL
)  ON [PRIMARY]
GO

-- 2. 鎖定整個 Table
ALTER TABLE dbo.Tmp_DataTypeModify SET (LOCK_ESCALATION = TABLE)
GO

-- 3. 把資料全部 INSERT 進入 Tmp_DataTypeModify 暫存 Table
IF EXISTS(SELECT * FROM dbo.DataTypeModify)
  EXEC('INSERT INTO dbo.Tmp_DataTypeModify (Data)
  SELECT CONVERT(date, Data) FROM dbo.DataTypeModify WITH (HOLDLOCK TABLOCKX)')
GO

-- 4. 刪除原有的 DataTypeModify Table
DROP TABLE dbo.DataTypeModify
GO

-- 5. 對 Tmp_DataTypeModify 進行更名
EXECUTE sp_rename N'dbo.Tmp_DataTypeModify', N'DataTypeModify', 'OBJECT' 
GO

COMMIT

利用 SQL Profile 來側錄的話，也可以發現上述步驟內容喔。

其實 SSMS 本身預設就有避免使用者更改的機制，透過 SSMS 去更改資料型態會被阻止

但是大家都會透過 [SQL] 不允許儲存變更 來取消掉這個限制吧 ~~ 哈 ~~

延伸閱讀
• [SQL] 不允許儲存變更
• [SQL] 利用 T-SQL 語法更改欄位資料型態
• [SQL] 利用 T-SQL 語法更改欄位名稱

參考資料
• 論壇問題相關討論
• Super SQL Server => 2016 企業版開始有 Online 參數可以使用
  
• [SQL]SQL Server 內資料上使用者權限移轉