架构更改表重建

发布于 2024-12-18 22:12:57 字数 84 浏览 4 评论 0原文

我正在编写一个脚本来保持表模式同步。

是否有对 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

野の 2024-12-25 22:12:57

您可能最好标准化 CREATE-COPY-DROP-RENAME (CCDR) 策略,并且仅在 DDL 不需要重建的少数情况下尝试就地更改,而不是尝试编译详尽的列表。这是此处描述的策略:链接< /a>.

AFAIK,只有在以下情况下才允许将列添加到现有表(无需重建):

  1. 添加到表末尾 AND
  2. 可为空或具有默认约束

在所有其他情况下,MSSQL如果它不知道在新添加的列的行中使用什么值,或者导致数据丢失(例如截断),则可能失败。即使在中间添加的默认列也会强制重建。

更复杂的是,在某些情况下,部署的成功将取决于表中的数据类型,而不仅仅是所涉及的架构。例如,将列长度更改为更大的值 (varchar(50) --> varchar(100))可能会成功;然而,只有有时允许减少长度。迁移数据类型更改是另一个棘手的问题。

简而言之,我会总是重建并且很少就地改变。

--

为了说明影响结果的行内数据:

create table dbo.Yak(s varchar(100));
insert into dbo.Yak
    values(replicate('a', 100));
go

-- attempt to alter datatype to 50 (FAIL: String or binary data would be truncated.)
alter table dbo.Yak 
    alter column s varchar(50);
go

-- shorten the data in row to avoid data loss
delete from dbo.Yak;
insert into dbo.Yak
    values(replicate('a', 50));
go

-- again, attempt to alter datatype to 50 (SUCCESS)
alter table dbo.Yak 
    alter column s varchar(50);
go

select len(s),* from dbo.Yak;
go

--cleanup
drop table dbo.Yak;
go

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:

  1. added to the end of the table AND
  2. is nullable or has a default constraint

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:

create table dbo.Yak(s varchar(100));
insert into dbo.Yak
    values(replicate('a', 100));
go

-- attempt to alter datatype to 50 (FAIL: String or binary data would be truncated.)
alter table dbo.Yak 
    alter column s varchar(50);
go

-- shorten the data in row to avoid data loss
delete from dbo.Yak;
insert into dbo.Yak
    values(replicate('a', 50));
go

-- again, attempt to alter datatype to 50 (SUCCESS)
alter table dbo.Yak 
    alter column s varchar(50);
go

select len(s),* from dbo.Yak;
go

--cleanup
drop table dbo.Yak;
go
温柔嚣张 2024-12-25 22:12:57

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

南风起 2024-12-25 22:12:57

我对使用 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.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文