强制Management Studio使用ALTER TABLE而不是DROP / CREATE [英] Forcing Management Studio to use ALTER TABLE instead of DROP/CREATE

问题描述

我想知道是否有一种方法可以强制MSSQL Management Studio生成这样的脚本：

I'm wondering if is there a way to force MSSQL Management Studio to produce a script like this:

ALTER TABLE Mytable
ADD MyCol bit NOT NULL
CONSTRAINT MyColDefault
DEFAULT 0 WITH VALUES


ALTER TABLE [dbo].Mytable
ALTER COLUMN MyCol2 int NULL

GO 

当我更改列的非常简单的属性时在桌子上。
如果我在设计器中执行此操作并要求生成的脚本，则该脚本不会执行此类简单任务，而是将所有数据复制到tmp表中，删除原始表，并使用原始表名。并且，当然，删除并重新创建每个约束和关系。

when I alter a very simple property of a column on a table. If I do this in the designer and ask for the produced script, the script doesn't do such simple tasks, but instead copies all the data in a tmp table, drops the original table, renames the tmp table with the original table name. And, of course, drops and recreates every constraint and relationships.

我是否可以更改任何选项来更改此行为？或者，这可能是可能的，在使用上面的简单ALTER TABLE时我没有看到一些危险吗？

Is there any option I can change to change this behaviour? Or, this may be possible, is there some danger I don't see in using the simple ALTER TABLE above?

谢谢。

推荐答案

您不能更改其行为，只是它创建的默认脚本并不总是最有效的。它以知道可以使用的格式创建脚本，尽管通常结果会很慢且占用大量资源。我建议您习惯于自己为所有更改创建脚本，因为您可以更好地对其进行优化。您创建的内容没有任何问题（假设您对MyCol2没有任何约束/依赖关系，而该约束/依赖关系将由于它成为可为null的int而无效）

You can't change the behaviour, it's just that the default script it creates is not always the most efficient. It creates scripts in a format it knows will work, although frequently the results will be slow and resource-heavy. I recommend you get used to creating the script for all changes yourself as you can better optimize it. There's nothing wrong with what you've created (providing you have no existing constraints/dependencies on MyCol2 that would be invalidated by it becoming a nullable int)

这篇关于强制Management Studio使用ALTER TABLE而不是DROP / CREATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！