“防止保存需要重新创建表的更改"负面影响
序言
我今天正在修改 SQL Server 2008 中的一个列，将数据类型从诸如货币(18,0)之类的内容更改为(19,2).
I was modifying a column in SQL Server 2008 today, changing the datatype from something like currency(18,0) to (19,2).
我从 SQL Server 收到错误您所做的更改需要删除并重新创建以下表".
I got the error "The changes you have made require the following tables to be dropped and re-created" from SQL Server.
在争先恐后地回答之前，请阅读以下内容:
我已经知道工具 ► 选项 ► 设计器 ► 表和数据库设计器 ► 取消选中复选框防止保存需要重新创建表的更改"中的选项....所以不要回答这个问题！
I already know there is the option in Tools ► Options ► Designer ► Table and Database Designers ► Uncheck the box "Prevent saving changes that require table re-creation." ...so do not answer with that!
实际问题
我的实际问题是别的，如下:
My actual question is for something else, as follows:
这样做是否有任何负面影响/可能的缺点?
Are there any negative effects / possible drawbacks of doing this?
当取消选中此框时，表实际上会自动删除并重新创建吗?
Does the table actually get dropped and recreated automatically when this box is unchecked?
如果是，该表是否是源表的 100% 精确副本?
If so, is the table copy a 100% exact replica of the source table?
推荐答案
只有在 SQL Server 的 Management Studio 被编程为知道如何执行此操作的唯一方式的情况下，才会删除并重新创建表.
The table is only dropped and re-created in cases where that's the only way SQL Server's Management Studio has been programmed to know how to do it.
当然在某些情况下它会在不需要时这样做，但也有一些情况下，您在 Management Studio 中所做的编辑将不会删除并重新创建，因为它不会不必.
There are certainly cases where it will do that when it doesn't need to, but there will also be cases where edits you make in Management Studio will not drop and re-create because it doesn't have to.
问题在于，枚举所有案例并确定它们落在线的哪一边将非常乏味.
The problem is that enumerating all of the cases and determining which side of the line they fall on will be quite tedious.
这就是为什么我喜欢使用 ALTER TABLE 在查询窗口中，而不是隐藏他们正在做的事情的视觉设计师(坦率地说有错误) - 我确切地知道会发生什么，而且我可以为唯一可能是删除并重新创建表的情况做好准备(这比 SSMS 对您执行此操作的频率要少一些).
This is why I like to use ALTER TABLE in a query window, instead of visual designers that hide what they're doing (and quite frankly have bugs) - I know exactly what is going to happen, and I can prepare for cases where the only possibility is to drop and re-create the table (which is some number less than how often SSMS will do that to you).
这样做是否有任何负面影响/可能的缺点?
Are there any negative effects / possible drawbacks of doing this?
当然.如果您可以在不重建整个表的情况下自己编写更改脚本，那就更好了 - 考虑表为 10TB，并且数据库被大量记录的情况(想想同步 AG、更改跟踪、复制、写得不好的触发器)和表被高度访问 - 这是灾难的潜在原因.如果您的更改是您可以应用在线提示或添加列并批量复制数据，而不是 GUI 将执行的全有或全无的更改，这会更好.
Sure. If you can script the change yourself without rebuilding the whole table, that's better - consider the case where the table is 10TB, and the database is heavily logged (think sync AG, change tracking, replication, poorly-written triggers), and the table is highly accessed - that's a potential recipe for disaster. If your change is something where you can apply an ONLINE hint or add a column and copy the data over in batches instead of all-or-nothing the GUI will do, this is better.
当取消选中此框时，表实际上会自动删除并重新创建吗?
Does the table actually get dropped and recreated automatically when this box is unchecked?
它可能.有一长串场景，结果取决于 SSMS 的版本、SQL Server 的版本，有时还取决于版本.您可以通过选中该框并尝试将更改首先应用于无意义的数据库副本来进行检查，但恕我直言，使用实际的 ALTER TABLE 脚本而不是点击式 GUI 是一种方法.
It might. There is a laundry list of scenarios and the outcome is dependent on the version of SSMS, the version of SQL Server, and sometimes the edition. You can check by checking the box and trying to apply the change on a meaningless copy of the database first, but using actual ALTER TABLE scripts instead of the pointy-clicky GUI is the way to go IMHO.
如果是，该表是否是源表的 100% 精确副本?
If so, is the table copy a 100% exact replica of the source table?
是的，如果 SSMS 必须重建表，它在完成后将是 100% 精确的副本(当然更改除外)，但这可能是下周三.该过程创建表的新版本，将所有数据复制到其中，然后删除旧表并重命名新表.
Yes, if SSMS has to rebuild the table, it will be a 100% exact replica after it's done (except for the change of course), but that could be next Wednesday. The process creates a new version of the table, copies all the data to it, then drop the old table and renames the new one.
相关文章