架构更改表重建
我正在编写一个脚本来保持表模式同步。
是否有对 MS SQL 中的表模式执行的操作的详尽列表,这些操作需要删除并重新创建表并重新插入数据?
I'm working on a script to keep table schemas synchronized.
Is there an exhaustive list of actions done to a table schema in MS SQL that requires the table to be dropped and recreated and the data to be reinserted?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
您可能最好标准化 CREATE-COPY-DROP-RENAME (CCDR) 策略,并且仅在 DDL 不需要重建的少数情况下尝试就地更改,而不是尝试编译详尽的列表。这是此处描述的策略:链接< /a>.
AFAIK,只有在以下情况下才允许将列添加到现有表(无需重建):
在所有其他情况下,MSSQL如果它不知道在新添加的列的行中使用什么值,或者导致数据丢失(例如截断),则可能失败。即使在中间添加的默认列也会强制重建。
更复杂的是,在某些情况下,部署的成功将取决于表中的数据类型,而不仅仅是所涉及的架构。例如,将列长度更改为更大的值 (
varchar(50) --> varchar(100)
)可能会成功;然而,只有有时允许减少长度。迁移数据类型更改是另一个棘手的问题。简而言之,我会总是重建并且很少就地改变。
--
为了说明影响结果的行内数据:
You may be better off standardizing on the CREATE-COPY-DROP-RENAME (CCDR) strategy and only attempting an in-place alter in the few scenarios where your DDL will not require a rebuild rather than trying to compile the exhaustive list. This is the strategy described here: link.
AFAIK, you are only permitted to add columns to an existing table (without rebuilding) if the column is:
In all other cases, MSSQL will potentially fail if it does not know what to use as a value in the rows of the newly added column or data loss is a result (truncation for example). Even defaulted columns added in the middle will force a rebuild.
To further complicate things, in some cases the success of your deploy will depend on the type of data in the table, and not simply the schema involved. For example, altering a column length to a greater value (
varchar(50) --> varchar(100)
) will likely succeed; however, decreasing the length is only sometimes permitted. Migrating data type changes is another tricky mess.In short, I would always rebuild and rarely alter in place.
--
To illustrate in-row data affecting outcome:
在 Management Studio 中,选择要更改的表并右键单击“设计”。在表设计窗口中更改列的数据类型(用int 测试为money)。
不要保存,而是在窗口中右键单击并选择“生成更改脚本”。从对话框中复制 SQL 语句。
*)在以前的版本(SQL2000)中,任何更改都会重新创建整个表(据我所知)。似乎重命名和添加列已针对 ALTER TABLE 语句进行了优化。
In Management Studio, select the table you want to change and right-click Design. Change the datatype of a column in the table design window (tested with int to money).
Instead of saving, right-click in the window and select "Generate Change Script". Copy the SQL statements from the dialog.
*) In previous versions (SQL2000), any changes would recreate the whole table (as far as I remember). It seems that renaming and adding columns have been optimized to ALTER TABLE statements.
我对使用 Visual Studio 数据库项目来管理此类事情感到非常满意。一旦我的模式导入到项目中,我就可以进行任何我想要的更改,VSDP 将确定是否可以在不删除对象的情况下完成更改(例如,使用 ALTER),或者是否需要创建一个新对象并从旧对象复制值(它会自动执行)。
计划做一些工作来了解如何将其适应您的特定环境和工作流程,但我发现这些努力非常值得。
I've gotten pretty spoiled by using Visual Studio Database Projects to manage this sort of thing. Once my schema is imported into a project, I can make whatever change I want, and the VSDP will figure out whether the change can be done w/o dropping objects (with an ALTER, for example), or whether it needs to create a new object and copy values over from the old one (which it does automatically).
Plan on a little work to understand how you'll fit this into your specific environment and workflow, but I've found the effort to be very worthwhile.