[SQL] 利用 T-SQL 語法更改欄位資料型態

同事利用 ALTER TABLE 語法更改欄位資料型態時，發現欄位本身有預設值，導致更改資料型態時產生 Error，SQL Server 提供的錯誤訊息明確，也很輕鬆的解決這個問題。

USE Tempdb
GO

IF OBJECT_ID('Demo') IS NOT NULL
  DROP TABLE Demo

CREATE TABLE Demo
  (
    ID INT IDENTITY(1,1) ,
    Col1 char(10) NOT NULL DEFAULT('') -- 預設為空值
  )

ALTER TABLE Demo ALTER COLUMN Col1 INT -- 更改資料型態為 INT

• 錯誤訊息

訊息 5074，層級 16，狀態 1，行 11

物件 'DF__Demo__Col1__35BCFEOA' 與資料行 'Col1' 相依。

查詢些資料來了解 ALTER TABLE 更改欄位資料型態時，要注意的事項

1. 欄位資料型態是 ROWGUIDCOL（GUID），只能進行刪除欄位，不允許更改欄位資料型態。
2. 計算欄位（computed column）或是複寫欄位（replicated column）無法變更資料型態。
3. 定義為 Primary Key 或 Foreign Key 的欄位，無法更改資料型態。
4. 更改欄位不可以參考計算爛位（computed column）。
5. 任何資料型態無法變更為 timestamp。
6. 欄位有設定 UNIQUE 或 CHECK 條件約束，只允許增加資料型態長度。
7. 欄位有設定 DEFAULT 值，只允許增加或減少資料型態長度、是否允許 NULL 值或改變有效位數（precision）或小數位值（scale）。
8. 舊資料型態必須允許隱含轉換（implicit conversion）至新資料型態。
9. 如果資料型態轉換過程中發生 overflow，轉換動作會被終止。
10. 轉換成新資料型態後，ANSI_PADDING 的設定一定是 ON。

下述兩點是實務上測試後有發現的資料，附上原文說明和自己的說明

1. The modified column can't be a text, image, ntext, or rowversion（timestamp）column.   ［text,image,ntext 或 rowversion（timestamp）無法變更資料型態］，可以針對 text、ntext 直接轉為 varchar(MAX)
2. If the modified column participates in an index, the only type changes that are allowed are increasing the length of a variable-length type (for example, varchar(10) to varchar(20), changing nullability of the column, or both) ［欄位是索引欄位，只允許增加資料型態長度或是否允許 NULL 的改變。］，測試 char、varchar 和 int 三種資料型態，只有 varchar 能直接增加長度，char 和 int 還是必須先卸除 Index，才能變化資料型態，另外 NULL 值只能從 NOT NULL 變成 NULL，無法從 NULL 改成 NOT NULL。

延伸閱讀
• [SQL] 利用 T-SQL 語法更改欄位名稱

MSDN 參考資料
• rowversion
• 有效位數、小數位數和長度
• column_definition
• SET ANSI_PADDING

參考資料
• Alter Table Drop Column
• [SQL SERVER][Performance] text 資料類型轉換 varchar(max)的影響
• SQL SERVER – How to alter an existing computed column in a table
• 技術論壇討論：一個 ALTER 語法更改多個欄位資料型態