更改列数据类型时保留 SQL 索引

Preserve SQL Indexes While Altering Column Datatype

提问by Phillip Benages

I have a smalldatetime column that I need to alter to be a datetime column. This is something that will be part of an install process, so it cannot be a manual procedure. Unfortunately, the column has a few indexes and a not null constraint on it. The indexes are performance related and would need to be retained only using the new data type. Is it possible to write a statement that will allow me to retain the relevant information while still altering the column datatype? If so, how can this be done?

我有一个 smalldatetime 列，我需要将其更改为 datetime 列。这是安装过程的一部分，因此它不能是手动过程。不幸的是，该列有几个索引和一个非空约束。索引与性能相关，仅需要使用新数据类型保留。是否可以编写一个语句，允许我在更改列数据类型的同时保留相关信息？如果是这样，如何做到这一点？

采纳答案by Shannon Severance

You can not change the datatype from smalldatetime to datetime with the indexes, unique constraints, foreign key constraints or check constraints in place. You will have to drop them all prior to changing the type. Then:

您不能将数据类型从 smalldatetime 更改为 datetime 并带有索引、唯一约束、外键约束或检查约束。在更改类型之前，您必须将它们全部删除。然后：

alter table T alter column TestDate datetime not null

Then recreate the constraints and indexes that still apply.

然后重新创建仍然适用的约束和索引。

---

---

Some different approaches for generating the drop and creates:

生成 drop 和 create 的一些不同方法：

1) If you have given explicit names to all indexes and constraints then your installer can run a static script in each environment (dev, test, user acceptance testing, performance testing, etc, production.)

1) 如果您为所有索引和约束指定了明确的名称，那么您的安装程序可以在每个环境（开发、测试、用户验收测试、性能测试等，生产环境）中运行静态脚本。

To generate this explicit script you can: a) Use SSMS (or with SQL Server 2000, enterprise manager) to script the create and drop statements. b) Work from you source code repository to discover the names and definitions of the dependent objects and put together the appropriate static script. c) Attempt to run the alter statement. See what it fails on. Look up the definitions and hand write the drop and create. (Personally, this would be great for writing the drop, not so good at the create.)

要生成此显式脚本，您可以： a) 使用 SSMS（或使用 SQL Server 2000、企业管理器）编写 create 和 drop 语句的脚本。b) 从您的源代码存储库中发现依赖对象的名称和定义，并将适当的静态脚本放在一起。c) 尝试运行alter 语句。看看它失败了什么。查找定义并手写删除并创建。（就个人而言，这对于编写 drop 来说会很棒，但在创建时不太好。）

2) If you have not given explicit names to all indexes and constraints, then your installer will have to query the data dictionary for the appropriate names and use dynamic SQL to run the drops, in the correct order, prior to the alter column statement and then the creates, in the correct order, after the alter column.

2) 如果您没有为所有索引和约束提供明确的名称，那么您的安装程序将必须在数据字典中查询适当的名称并使用动态 SQL 以正确的顺序在更改列语句和然后以正确的顺序在更改列之后创建。

This will be simpler if you know that there are no constraints, and just indexes.

如果您知道没有约束，只有索引，这会更简单。

There may be tools or libraries that already know how to do this.

可能有一些工具或库已经知道如何做到这一点。

Also, if this is a packaged application, you may not be assured that the local DBAs have not added indexes.

此外，如果这是一个打包的应用程序，您可能无法确定本地 DBA 没有添加索引。

NOTE: If there is a unique constraint, it will have built an index, which you will not be able to drop with DROP INDEX.

注意：如果存在唯一约束，它将建立一个索引，您将无法使用 DROP INDEX 删除该索引。

回答by Raj More

If you are just changing the size, the Index will still remain on the table.

如果您只是更改大小，索引仍将保留在表中。

If you are changing the data type, then you will get an error message stating that objects depend on the column that you are trying to change and therefore you will not be able to change it.

如果您正在更改数据类型，则会收到一条错误消息，指出对象依赖于您尝试更改的列，因此您将无法更改它。

You can script out the indexes in question manually or via script. In SSMS, right click the table and script out the object in question.

您可以手动或通过脚本编写有问题的索引。在 SSMS 中，右键单击表并编写相关对象的脚本。

If you want programatic index scripting, here is a stored proc that I have been using that I got from an ex colleague of mine.

如果你想要编程索引脚本，这里有一个我一直在使用的存储过程，我从我的一个前同事那里得到的。

Drop Proc ScriptIndex
GO
Create Proc ScriptIndex
    @TableName      VarChar (Max),
    @IndexScript    VarChar (Max) OUTPUT
AS

-- Get all existing indexes, EXCEPT the primary keys
DECLARE cIX CURSOR FOR
SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID
FROM Sys.Indexes SI 
    LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC 
        ON SI.Name = TC.CONSTRAINT_NAME 
        AND OBJECT_NAME(SI.Object_ID) = TC.TABLE_NAME
WHERE 1=1
    AND OBJECT_NAME(SI.Object_ID) = @TableName
    AND TC.CONSTRAINT_NAME IS NULL
    AND OBJECTPROPERTY(SI.Object_ID, 'IsUserTable') = 1
ORDER BY OBJECT_NAME(SI.Object_ID), SI.Index_ID

DECLARE @IxTable SYSNAME
DECLARE @IxTableID INT
DECLARE @IxName SYSNAME
DECLARE @IxID INT

-- Loop through all indexes
OPEN cIX
FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
WHILE (@@FETCH_STATUS = 0)
BEGIN
   DECLARE @IXSQL NVARCHAR(4000) 
   DECLARE @PKSQL NVARCHAR(4000) 
   SET @PKSQL = ''
   SET @IXSQL = 'CREATE '

   -- Check if the index is unique
   IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)
      SET @IXSQL = @IXSQL + 'UNIQUE '
   -- Check if the index is clustered
   IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)
      SET @IXSQL = @IXSQL + 'CLUSTERED '

   SET @IXSQL = @IXSQL + 'INDEX ' + @IxName + ' ON ' + @IxTable + '('

   -- Get all columns of the index
   DECLARE cIxColumn CURSOR FOR 
      SELECT SC.Name
      FROM Sys.Index_Columns IC
         JOIN Sys.Columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID
      WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID
      ORDER BY IC.Index_Column_ID

   DECLARE @IxColumn SYSNAME
   DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1

   -- Loop throug all columns of the index and append them to the CREATE statement
   OPEN cIxColumn
   FETCH NEXT FROM cIxColumn INTO @IxColumn
   WHILE (@@FETCH_STATUS = 0)
   BEGIN
      IF (@IxFirstColumn = 1)
         SET @IxFirstColumn = 0
      ELSE
         SET @IXSQL = @IXSQL + ', '

      SET @IXSQL = @IXSQL + @IxColumn

      FETCH NEXT FROM cIxColumn INTO @IxColumn
   END
   CLOSE cIxColumn
   DEALLOCATE cIxColumn

   SET @IXSQL = @IXSQL + ')'
   -- Print out the CREATE statement for the index
   PRINT @IXSQL

   FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
END

CLOSE cIX
DEALLOCATE cIX

GO
Declare @TableName VarChar (Max), @IndexScript VarChar (Max)

Exec ScriptIndex 'Client', @IndexScript OUTPUT
Print @IndexScript

回答by Mitch Wheat

EDIT: It depends on the original and changed datatype. If you try to alter a column from varchar to nvarchar, it will fail. Whereas, if you alter column from varchar(16) to varchar(32), it will succeed.

编辑：这取决于原始和更改后的数据类型。如果您尝试将列从 varchar 更改为 nvarchar，它将失败。然而，如果您将列从 varchar(16) 更改为 varchar(32)，它将成功。

--Disable Index
ALTER INDEX MyIndex ON MyTable DISABLE
GO

-- Change column datatype

--Enable Index
ALTER INDEX MyIndex ON MyTable REBUILD
GO

If you change the type of a column, then all indexes that use that column will have to be rebuilt.

如果更改列的类型，则必须重建使用该列的所有索引。

But unless you have huge volumes of data (or run 24/7), rebuilding indexes is no big deal. Just schedule a maintenance window.

但是除非您拥有大量数据（或 24/7 全天候运行），否则重建索引没什么大不了的。只需安排一个维护窗口。

回答by Diogo Rodrigues

The best thing to do is to create a procedure that returns the index script of a given table / column. So you can remove the indexes just from the column being altered and not all indexes from the table, whereas creating indices can be somewhat expensive.

最好的办法是创建一个返回给定表/列的索引脚本的过程。因此，您可以仅从正在更改的列中删除索引，而不是从表中删除所有索引，而创建索引可能会有些昂贵。

1. Stores the result of the procedure in a datatable
2. Delete the indices of the column
3. Modify your column

4. Rebuild the indexes stored in the datatable

   -- objective   : Generates indices scripting using specified column
   -- Parameters : 
   --     @Tabela  -> Name of the table that the column belongs to 
   --     @Coluna -> Name of the column that will be searched for the indices to generate the script
   --Use: proc_ScriptIndexColumn 'TableName', 'ColumnName'
   
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   Create Proc proc_ScriptIndexColumn (@Tabela VARCHAR(4000), @Coluna VARCHAR(4000)) 
   AS 
   BEGIN     
        DECLARE @isql_key VARCHAR(4000), 
            @isql_incl VARCHAR(4000), 
            @tableid INT, 
            @indexid INT         
    DECLARE @tablename VARCHAR(4000), 
            @indexname VARCHAR(4000)         
    DECLARE @isunique INT, 
            @isclustered INT, 
            @indexfillfactor INT         
    DECLARE @srsql VARCHAR(MAX)        
    DECLARE @ScriptsRetorno TABLE 
            (Script VARCHAR(MAX))        
    DECLARE index_cursor CURSOR  
      FOR 
        SELECT tablename = OBJECT_NAME(i.[object_id]), 
               tableid       = i.[object_id], 
               indexid       = i.index_id, 
               indexname     = i.name, 
               isunique      = i.is_unique, 
               CASE I.type_desc 
                    WHEN 'CLUSTERED' THEN 1 
                    ELSE 0 
               END                     AS isclustered, 
               indexfillfactor = i.fill_factor                  
        FROM   sys.indexes             AS i 
               INNER JOIN SYSOBJECTS   AS O 
                    ON  I.[object_id] = O.ID 
               INNER JOIN sys.index_columns AS ic 
                    ON  (ic.column_id > 0 
                            AND (ic.key_ordinal > 0 
                                    OR ic.partition_ordinal = 0 
                                    OR ic.is_included_column != 0 
                                )) 
                    AND (   ic.index_id = CAST(i.index_id AS INT) 
                            AND ic.object_id = i.[object_id] 
                        ) 
               INNER JOIN sys.columns  AS sc 
                       ON  sc.object_id = ic.object_id 
                    AND sc.column_id = ic.column_id 
        WHERE  O.XTYPE = 'U' 
               AND i.typE = 2 /*Non clustered*/ 
               AND i.is_unique = 0 
               AND i.is_hypothetical = 0 
               AND UPPER(OBJECT_NAME(i.[object_id])) = UPPER(@Tabela) 
               AND UPPER(sc.name) = UPPER(@Coluna)       
   
    OPEN index_cursor  
    FETCH NEXT FROM index_cursor INTO @tablename,@tableid, @indexid,@indexname ,  
    @isunique ,@isclustered , @indexfillfactor       
    WHILE @@fetch_status <> -1 
    BEGIN 
        SELECT @isql_key = '', 
               @isql_incl = ''           
        SELECT @isql_key = CASE ic.is_included_column 
                                WHEN 0 THEN CASE ic.is_descending_key 
                                                 WHEN 1 THEN @isql_key +COALESCE(sc.name, '') + 
                                                      ' DESC, ' 
                                                 ELSE @isql_key + COALESCE(sc.name, '')  
                                                      + ' ASC, ' 
                                            END 
                                ELSE @isql_key 
                            END, 
               --include column  
               @isql_incl = CASE ic.is_included_column 
                                 WHEN 1 THEN CASE ic.is_descending_key 
                                                  WHEN 1 THEN @isql_incl + 
                                                       COALESCE(sc.name, '') + 
                                                       ', ' 
                                                  ELSE @isql_incl + COALESCE(sc.name, '')  
                                                       + ', ' 
                                             END 
                                 ELSE @isql_incl 
                            END 
        FROM   sysindexes i 
               INNER JOIN sys.index_columns AS ic 
                    ON  ( 
                            ic.column_id > 0 
                            AND ( 
                                    ic.key_ordinal > 0 
                                    OR ic.partition_ordinal = 0 
                                    OR ic.is_included_column != 0 
                                ) 
                        ) 
                    AND (ic.index_id = CAST(i.indid AS INT) AND ic.object_id = i.id) 
               INNER JOIN sys.columns AS sc 
                      ON  sc.object_id = ic.object_id 
                    AND sc.column_id = ic.column_id 
        WHERE  i.indid > 0 
               AND i.indid < 255 
               AND (i.status & 64) = 0 
               AND i.id = @tableid 
               AND i.indid = @indexid 
        ORDER BY 
               i.name, 
               CASE ic.is_included_column 
                    WHEN 1 THEN ic.index_column_id 
                    ELSE ic.key_ordinal 
               END           
        IF LEN(@isql_key) > 1 
            SET @isql_key = LEFT(@isql_key, LEN(@isql_key) -1)  
   
        IF LEN(@isql_incl) > 1 
            SET @isql_incl = LEFT(@isql_incl, LEN(@isql_incl) -1)            
        SET @srsql = 'CREATE ' + 'INDEX [' + @indexname + ']' + ' ON [' + @tablename 
            + '] '           
        SET @srsql = @srsql + '(' + @isql_key + ')'              
        IF (@isql_incl <> '') 
            SET @srsql = @srsql + ' INCLUDE(' + @isql_incl + ')'             
        IF (@indexfillfactor <> 0) 
             SET @srsql = @srsql + ' WITH ( FILLFACTOR = ' + CONVERT(VARCHAR(10), @indexfillfactor) 
                + ')'            
        FETCH NEXT FROM index_cursor INTO @tablename,@tableid,@indexid,@indexname,  
        @isunique ,@isclustered , @indexfillfactor           
        INSERT INTO @ScriptsRetorno 
        VALUES 
          (@srsql) 
    END  
    CLOSE index_cursor  
    DEALLOCATE index_cursor   
    SELECT * 
    FROM   @ScriptsRetorno 
   RETURN @@ERROR 
   END 
   

1. 将过程的结果存储在数据表中
2. 删除列的索引
3. 修改你的专栏

4. 重建存储在数据表中的索引

   -- objective   : Generates indices scripting using specified column
   -- Parameters : 
   --     @Tabela  -> Name of the table that the column belongs to 
   --     @Coluna -> Name of the column that will be searched for the indices to generate the script
   --Use: proc_ScriptIndexColumn 'TableName', 'ColumnName'
   
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   Create Proc proc_ScriptIndexColumn (@Tabela VARCHAR(4000), @Coluna VARCHAR(4000)) 
   AS 
   BEGIN     
        DECLARE @isql_key VARCHAR(4000), 
            @isql_incl VARCHAR(4000), 
            @tableid INT, 
            @indexid INT         
    DECLARE @tablename VARCHAR(4000), 
            @indexname VARCHAR(4000)         
    DECLARE @isunique INT, 
            @isclustered INT, 
            @indexfillfactor INT         
    DECLARE @srsql VARCHAR(MAX)        
    DECLARE @ScriptsRetorno TABLE 
            (Script VARCHAR(MAX))        
    DECLARE index_cursor CURSOR  
      FOR 
        SELECT tablename = OBJECT_NAME(i.[object_id]), 
               tableid       = i.[object_id], 
               indexid       = i.index_id, 
               indexname     = i.name, 
               isunique      = i.is_unique, 
               CASE I.type_desc 
                    WHEN 'CLUSTERED' THEN 1 
                    ELSE 0 
               END                     AS isclustered, 
               indexfillfactor = i.fill_factor                  
        FROM   sys.indexes             AS i 
               INNER JOIN SYSOBJECTS   AS O 
                    ON  I.[object_id] = O.ID 
               INNER JOIN sys.index_columns AS ic 
                    ON  (ic.column_id > 0 
                            AND (ic.key_ordinal > 0 
                                    OR ic.partition_ordinal = 0 
                                    OR ic.is_included_column != 0 
                                )) 
                    AND (   ic.index_id = CAST(i.index_id AS INT) 
                            AND ic.object_id = i.[object_id] 
                        ) 
               INNER JOIN sys.columns  AS sc 
                       ON  sc.object_id = ic.object_id 
                    AND sc.column_id = ic.column_id 
        WHERE  O.XTYPE = 'U' 
               AND i.typE = 2 /*Non clustered*/ 
               AND i.is_unique = 0 
               AND i.is_hypothetical = 0 
               AND UPPER(OBJECT_NAME(i.[object_id])) = UPPER(@Tabela) 
               AND UPPER(sc.name) = UPPER(@Coluna)       
   
    OPEN index_cursor  
    FETCH NEXT FROM index_cursor INTO @tablename,@tableid, @indexid,@indexname ,  
    @isunique ,@isclustered , @indexfillfactor       
    WHILE @@fetch_status <> -1 
    BEGIN 
        SELECT @isql_key = '', 
               @isql_incl = ''           
        SELECT @isql_key = CASE ic.is_included_column 
                                WHEN 0 THEN CASE ic.is_descending_key 
                                                 WHEN 1 THEN @isql_key +COALESCE(sc.name, '') + 
                                                      ' DESC, ' 
                                                 ELSE @isql_key + COALESCE(sc.name, '')  
                                                      + ' ASC, ' 
                                            END 
                                ELSE @isql_key 
                            END, 
               --include column  
               @isql_incl = CASE ic.is_included_column 
                                 WHEN 1 THEN CASE ic.is_descending_key 
                                                  WHEN 1 THEN @isql_incl + 
                                                       COALESCE(sc.name, '') + 
                                                       ', ' 
                                                  ELSE @isql_incl + COALESCE(sc.name, '')  
                                                       + ', ' 
                                             END 
                                 ELSE @isql_incl 
                            END 
        FROM   sysindexes i 
               INNER JOIN sys.index_columns AS ic 
                    ON  ( 
                            ic.column_id > 0 
                            AND ( 
                                    ic.key_ordinal > 0 
                                    OR ic.partition_ordinal = 0 
                                    OR ic.is_included_column != 0 
                                ) 
                        ) 
                    AND (ic.index_id = CAST(i.indid AS INT) AND ic.object_id = i.id) 
               INNER JOIN sys.columns AS sc 
                      ON  sc.object_id = ic.object_id 
                    AND sc.column_id = ic.column_id 
        WHERE  i.indid > 0 
               AND i.indid < 255 
               AND (i.status & 64) = 0 
               AND i.id = @tableid 
               AND i.indid = @indexid 
        ORDER BY 
               i.name, 
               CASE ic.is_included_column 
                    WHEN 1 THEN ic.index_column_id 
                    ELSE ic.key_ordinal 
               END           
        IF LEN(@isql_key) > 1 
            SET @isql_key = LEFT(@isql_key, LEN(@isql_key) -1)  
   
        IF LEN(@isql_incl) > 1 
            SET @isql_incl = LEFT(@isql_incl, LEN(@isql_incl) -1)            
        SET @srsql = 'CREATE ' + 'INDEX [' + @indexname + ']' + ' ON [' + @tablename 
            + '] '           
        SET @srsql = @srsql + '(' + @isql_key + ')'              
        IF (@isql_incl <> '') 
            SET @srsql = @srsql + ' INCLUDE(' + @isql_incl + ')'             
        IF (@indexfillfactor <> 0) 
             SET @srsql = @srsql + ' WITH ( FILLFACTOR = ' + CONVERT(VARCHAR(10), @indexfillfactor) 
                + ')'            
        FETCH NEXT FROM index_cursor INTO @tablename,@tableid,@indexid,@indexname,  
        @isunique ,@isclustered , @indexfillfactor           
        INSERT INTO @ScriptsRetorno 
        VALUES 
          (@srsql) 
    END  
    CLOSE index_cursor  
    DEALLOCATE index_cursor   
    SELECT * 
    FROM   @ScriptsRetorno 
   RETURN @@ERROR 
   END