参与了索引的列 怎么更改数据类型？

Question

参与了索引的列 怎么更改数据类型？ alter table a alter column aa int

Answer 1

你需要先删除索引，修改完列再重建，删除和重建的SQL可以通过查询自动生成，比如：
删除相关索引语句：

 DECLARE @indexName VARCHAR(500)=''
DECLARE @TableName VARCHAR(500)='t2'
DECLARE @ColName VARCHAR(500)='paid'

SELECT 'DROP INDEX '+idx.name+' ON '+@TableName
FROM sys.indexes idx
INNER JOIN sys.tables tbl ON idx.object_id = tbl.object_id
INNER JOIN sys.index_columns idxCol ON idx.index_id = idxCol.index_id
INNER JOIN sys.columns col ON idxCol.column_id = col.column_id
WHERE idx.type <> 0
    AND tbl.NAME = @TableName
    AND col.NAME = @ColName

新建语句

@indexName 是上面查询返回的索引名称

 SELECT  'CREATE NONCLUSTERED INDEX '+@indexName+' ON '+@TableName+'('+STUFF( 
(SELECT ','+col.name+ CASE WHEN idxcol.is_descending_key=1 THEN ' DESC' ELSE '' END
FROM sys.indexes idx
INNER JOIN sys.index_columns idxCol ON idx.index_id = idxCol.index_id
INNER JOIN sys.columns col ON idxCol.column_id = col.column_id AND col.[object_id]=idx.[object_id]
WHERE idx.name=@indexName
FOR XML PATH('')
),1,1,'')+')'