更改列数据类型时保留 SQL 索引 [英] Preserve SQL Indexes While Altering Column Datatype

问题描述

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

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.在更改类型之前，您必须将它们全部删除.然后:

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:

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:

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

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.)

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

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.)

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

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.

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

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.

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

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

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

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

这篇关于更改列数据类型时保留 SQL 索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！