需要更改生产数据库中的列类型 (SQL Server 2005) [英] Need to alter column types in production database (SQL Server 2005)

问题描述

我需要帮助编写 TSQL 脚本来修改两列的数据类型.

I need help writing a TSQL script to modify two columns' data type.

我们正在更改两列:

• uniqueidentifier -> varchar(36) * * * 有主键约束
• xml -> nvarchar(4000)

我主要关心的是脚本的生产部署...

My main concern is production deployment of the script...

该表被一个每小时获得数千次点击的公共网站积极使用.因此，我们需要脚本快速运行，同时不影响前端的服务.另外，如果发生错误，我们需要能够自动回滚事务.

The table is actively used by a public website that gets thousands of hits per hour. Consequently, we need the script to run quickly, without affecting service on the front end. Also, we need to be able to automatically rollback the transaction if an error occurs.

幸运的是，该表仅包含大约 25 行，所以我猜更新会很快.

Fortunately, the table only contains about 25 rows, so I am guessing the update will be quick.

此数据库是 SQL Server 2005.

This database is SQL Server 2005.

(仅供参考 - 由于第 3 方工具与 SQL Server 的 xml 和 uniqueidentifier 类型不兼容，因此需要更改类型.我们已经在 dev 中测试了更改，并且更改没有功能问题.)

(FYI - the type changes are required because of a 3rd-party tool which is not compatible with SQL Server's xml and uniqueidentifier types. We've already tested the change in dev and there are no functional issues with the change.)

推荐答案

正如 David 所说，在生产数据库中执行脚本而不做备份或停止站点并不是最好的主意，也就是说，如果你想这样做仅在一个行数减少的表中进行更改，您可以准备一个脚本来:

As David said, execute a script in a production database without doing a backup or stop the site is not the best idea, that said, if you want to do changes in only one table with a reduced number of rows you can prepare a script to :

• 开始交易
• 创建一个包含最终结果的新表你想要的结构.
• 从原表复制数据到新表
• 将旧表重命名为，例如，original_name_old
• 将新表重命名为original_table_name
• 结束交易

这将以一个名为原始表但具有您想要的新结构的表结束，此外，您使用备份名称维护原始表，因此如果您想回滚更改，您可以创建一个脚本简单地删除新表并重命名原始表.

This will end with a table that is named as the original one but with the new structure you want, and in addition you maintain the original table with a backup name, so if you want to rollback the change you can create a script to do a simple drop of the new table and rename of the original one.

如果表有外键，脚本会稍微复杂一些，但仍然可以不用太多工作.

If the table has foreign keys the script will be a little more complicated, but is still possible without much work.

这篇关于需要更改生产数据库中的列类型 (SQL Server 2005)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！