“防止保存需要重新创建表的更改”;负面影响

序言

我今天在SQL Server 2008中修改了一个列，将数据类型从货币(18,0)更改为(19,2)。

我从SQL Server中得到错误“您所做的更改需要删除并重新创建以下表”。

在你急着回答之前，请先阅读以下内容:

我已经知道在工具►选项►设计器►表和数据库设计器►取消复选框中有一个选项“防止保存需要重新创建表的更改”。 防止保存需要在五次单击中重新创建表的更改 .．.所以不回答那个!< / p >

实际的问题

我的实际问题是关于其他事情的，具体如下:

这样做有什么负面影响/可能的缺点吗?

当这个框未选中时，表是否会被自动删除并重新创建?

如果是，表副本是源表的100%精确副本吗?

183516 次浏览
小开
最佳答案

只有在SQL Server Management Studio编程知道如何删除和重新创建表的情况下，该表才会被删除和重新创建。

当然，在某些情况下，它会在不需要的情况下这样做，但也会在某些情况下，你在Management Studio中所做的编辑将不丢弃并重新创建，因为它不需要这样做。

问题是，列举所有情况并确定它们属于界限的哪一边将是相当乏味的。

这就是为什么我喜欢在查询窗口中使用ALTER TABLE，而不是隐藏他们正在做的事情的可视化设计器(坦率地说有bug) -我确切地知道将会发生什么，并且我可以为唯一可能是删除并重新创建表的情况做好准备(这比SSMS对你做这件事的频率少一些)。

这样做有什么负面影响/可能的缺点吗?

当然。如果您可以自己编写更改脚本，而不需要重新构建整个表，那就更好了——考虑这样一种情况，表是10TB，数据库日志记录很重(考虑同步AG、更改跟踪、复制、编写得很差的触发器)，并且表被高度访问——这可能会导致灾难。如果您的更改是可以应用ONLINE提示或添加一列并批量复制数据，而不是GUI所做的全有或全无，那么这样做会更好。

当这个框未选中时，表是否会被自动删除并重新创建?

可能。场景有一个洗衣清单，结果取决于SSMS的版本，SQL Server的版本，有时是版本。你可以通过勾选复选框并尝试应用更改首先在一个毫无意义的数据库副本上来进行检查，但使用实际的ALTER TABLE脚本而不是点式单击GUI是IMHO的方式。

如果是，表副本是源表的100%精确副本吗?

是的，如果SSMS必须重新构建表，那么在重建完成后，它将是一个100%精确的副本(当然，更改除外)，但这可能是下周三。该进程创建一个新版本的表，将所有数据复制到其中，然后删除旧表并重命名新表。

小开

参考< a href = " http://support.microsoft.com/kb/956176 " > < / > -关闭这个选项可以帮助你避免重新创建一个表，它也可以导致更改丢失。例如，假设您启用SQL Server 2008中的Change Tracking特性来跟踪表的更改。在执行导致重新创建表的操作时，将收到“症状”一节中提到的错误消息。但是，如果关闭此选项，则在重新创建表时将删除现有的更改跟踪信息。因此，Microsoft建议您不要通过关闭该选项来解决此问题。

小开

SQL Server仅在以下情况下删除并重新创建表:

  • 添加一个新列
  • 更改列的允许空值设置
  • 更改表中的列顺序
  • 更改列数据类型

使用ALTER更安全，因为如果在重新创建表时丢失了元数据，您的数据也会丢失。

小开

防止保存需要重新创建表的更改

小开

是的，这也有负面影响:

如果你用脚本删除一个被这个标志阻止的更改，你会得到类似下面脚本的东西(我把Contact中的ID列变成了一个自动编号的IDENTITY列，但是这个表有依赖关系)。 注意在运行以下命令时可能发生的潜在错误:

  1. 甚至微软也警告说这可能会导致数据丢失(该评论是自动生成的)!
  2. 在一段时间内，不强制使用外键。
  3. 如果您手动在ssms中运行这个，并且' EXEC('INSERT INTO '失败，并且您让以下语句运行(默认情况下，因为它们被'go'分割)，那么您将插入0行，然后删除旧表。
  4. 如果这是一个大表，插入的运行时可以很大，并且事务持有一个模式修改锁，所以阻塞许多东西。

--

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/


BEGIN TRANSACTION
GO
ALTER TABLE raw.Contact
DROP CONSTRAINT fk_Contact_AddressType
GO
ALTER TABLE ref.ContactpointType SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE raw.Contact
DROP CONSTRAINT fk_contact_profile
GO
ALTER TABLE raw.Profile SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE raw.Tmp_Contact
(
ContactID int NOT NULL IDENTITY (1, 1),
ProfileID int NOT NULL,
AddressType char(2) NOT NULL,
ContactText varchar(250) NULL
)  ON [PRIMARY]
GO
ALTER TABLE raw.Tmp_Contact SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT raw.Tmp_Contact ON
GO
IF EXISTS(SELECT * FROM raw.Contact)
EXEC('INSERT INTO raw.Tmp_Contact (ContactID, ProfileID, AddressType, ContactText)
SELECT ContactID, ProfileID, AddressType, ContactText FROM raw.Contact WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT raw.Tmp_Contact OFF
GO
ALTER TABLE raw.PostalAddress
DROP CONSTRAINT fk_AddressProfile
GO
ALTER TABLE raw.MarketingFlag
DROP CONSTRAINT fk_marketingflag_contact
GO
ALTER TABLE raw.Phones
DROP CONSTRAINT fk_phones_contact
GO
DROP TABLE raw.Contact
GO
EXECUTE sp_rename N'raw.Tmp_Contact', N'Contact', 'OBJECT'
GO
ALTER TABLE raw.Contact ADD CONSTRAINT
Idx_Contact_1 PRIMARY KEY CLUSTERED
(
ProfileID,
ContactID
)


GO
ALTER TABLE raw.Contact ADD CONSTRAINT
Idx_Contact UNIQUE NONCLUSTERED
(
ProfileID,
ContactID
)


GO
CREATE NONCLUSTERED INDEX idx_Contact_0 ON raw.Contact
(
AddressType
)
GO
ALTER TABLE raw.Contact ADD CONSTRAINT
fk_contact_profile FOREIGN KEY
(
ProfileID
) REFERENCES raw.Profile
(
ProfileID
) ON UPDATE  NO ACTION
ON DELETE  NO ACTION


GO
ALTER TABLE raw.Contact ADD CONSTRAINT
fk_Contact_AddressType FOREIGN KEY
(
AddressType
) REFERENCES ref.ContactpointType
(
ContactPointTypeCode
) ON UPDATE  NO ACTION
ON DELETE  NO ACTION


GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE raw.Phones ADD CONSTRAINT
fk_phones_contact FOREIGN KEY
(
ProfileID,
PhoneID
) REFERENCES raw.Contact
(
ProfileID,
ContactID
) ON UPDATE  NO ACTION
ON DELETE  NO ACTION


GO
ALTER TABLE raw.Phones SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE raw.MarketingFlag ADD CONSTRAINT
fk_marketingflag_contact FOREIGN KEY
(
ProfileID,
ContactID
) REFERENCES raw.Contact
(
ProfileID,
ContactID
) ON UPDATE  NO ACTION
ON DELETE  NO ACTION


GO
ALTER TABLE raw.MarketingFlag SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE raw.PostalAddress ADD CONSTRAINT
fk_AddressProfile FOREIGN KEY
(
ProfileID,
AddressID
) REFERENCES raw.Contact
(
ProfileID,
ContactID
) ON UPDATE  NO ACTION
ON DELETE  NO ACTION


GO
ALTER TABLE raw.PostalAddress SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

提交答案