SQL Server修改帶索引列的數據類型：刪除索引→修改列→重建索引完整腳本

SQL Server中參與索引的列無法直接`ALTER COLUMN`修改數據類型，必須先刪除相關索引，修改列，再重建索引。本文提供自動生成刪除/重建腳本的動態SQL方案。

操作流程

1. 查詢該列涉及的所有索引（含主鍵、唯一、普通、包含列）。
2. 生成並執行`DROP INDEX`腳本。
3. 執行`ALTER TABLE ... ALTER COLUMN ...`修改類型。
4. 生成並執行`CREATE INDEX`腳本恢復索引。

動態生成刪除索引腳本

DECLARE @TableName sysname = 'YourTable';
DECLARE @ColName sysname = 'YourColumn';

DECLARE @DropIndexSQL NVARCHAR(MAX) = N'';

SELECT @DropIndexSQL += 
    'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + '.' + QUOTENAME(OBJECT_NAME(i.object_id)) + ';' + CHAR(13)
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.type > 0  -- 排除堆表 (type=0)
  AND OBJECT_NAME(i.object_id) = @TableName
  AND c.name = @ColName
  AND i.is_primary_key = 0  -- 主鍵需單獨處理 (ALTER TABLE DROP CONSTRAINT)
  AND i.is_unique_constraint = 0; -- 唯一約束同理

PRINT @DropIndexSQL;
-- EXEC sp_executesql @DropIndexSQL; -- 驗證無誤後執行

動態生成重建索引腳本

DECLARE @CreateIndexSQL NVARCHAR(MAX) = N'';

SELECT @CreateIndexSQL +=
    'CREATE ' + 
    CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END +
    CASE WHEN i.type = 1 THEN 'CLUSTERED ' ELSE 'NONCLUSTERED ' END +
    'INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + '.' + QUOTENAME(OBJECT_NAME(i.object_id)) + ' (' +
    STUFF((
        SELECT ', ' + QUOTENAME(c.name) + 
               CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END
        FROM sys.index_columns ic
        JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
        ORDER BY ic.key_ordinal
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') +
    ')' +
    -- 包含列
    ISNULL(' INCLUDE (' + STUFF((
        SELECT ', ' + QUOTENAME(c.name)
        FROM sys.index_columns ic
        JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1
        ORDER BY ic.index_column_id
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')', '') +
    ' WITH (ONLINE = ON' +  -- 企業版支持在線重建，減少鎖定
    CASE WHEN i.fill_factor > 0 THEN ', FILLFACTOR = ' + CAST(i.fill_factor AS VARCHAR) ELSE '' END +
    CASE WHEN i.ignore_dup_key = 1 THEN ', IGNORE_DUP_KEY = ON' ELSE '' END +
    CASE WHEN i.allow_row_locks = 0 THEN ', ALLOW_ROW_LOCKS = OFF' ELSE '' END +
    CASE WHEN i.allow_page_locks = 0 THEN ', ALLOW_PAGE_LOCKS = OFF' ELSE '' END +
    CASE WHEN i.data_compression_desc != 'NONE' THEN ', DATA_COMPRESSION = ' + i.data_compression_desc ELSE '' END +
    ');' + CHAR(13)
FROM sys.indexes i
WHERE i.type > 0
  AND OBJECT_NAME(i.object_id) = @TableName
  AND EXISTS (
    SELECT 1 FROM sys.index_columns ic
    JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND c.name = @ColName
  );

PRINT @CreateIndexSQL;
-- EXEC sp_executesql @CreateIndexSQL;

主鍵/唯一約束特殊處理

-- 主鍵
ALTER TABLE YourTable DROP CONSTRAINT PK_YourTable;
ALTER TABLE YourTable ALTER COLUMN YourColumn NEW_TYPE;
ALTER TABLE YourTable ADD CONSTRAINT PK_YourTable PRIMARY KEY CLUSTERED (YourColumn); -- 或其他列

-- 唯一約束
ALTER TABLE YourTable DROP CONSTRAINT UQ_YourTable_YourColumn;
-- ALTER COLUMN ...
ALTER TABLE YourTable ADD CONSTRAINT UQ_YourTable_YourColumn UNIQUE (YourColumn);

最佳實踐建議

• 事務包裹：將DROP、ALTER、CREATE包在顯式事務中（注意DDL隱式提交，SQL Server不支持DDL回滾，需謹慎）。
• 低峰期執行：大表操作鎖表時間長，安排維護窗口。
• 備份先行：操作前完整備份數據庫。
• ONLINE = ON：企業版重建索引指定`ONLINE=ON`避免長時間鎖表。
• 驗證腳本：先生成腳本`PRINT`審核，確認索引定義無誤再執行。

核心原則：索引依賴列定義，修改列必須先拆索引，改完再裝回去，動態SQL讓這過程可控、可審計、可復用。