防止保存需要重新创建表的更改会产生负面影响。

Question

防止保存需要重新创建表的更改会产生负面影响。

sql-serversql-server-2008database-designsql-server-2005

272

前言

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

我从 SQL Server 收到了错误信息 "您所做的更改需要删除并重新创建以下表"。

请仔细阅读以下内容再回答问题：

我已经知道在 工具 ► 选项 ► 设计人员 ► 表和数据库设计师 ► 取消选中方框 "防止保存需要重新创建表的更改" 中的选项。 ...所以不要用那个来回答我的问题！

实际问题

我的实际问题与其他事情有关，具体如下：

这样做会有任何负面影响或可能的缺陷吗？

当取消选择此框时，表是否实际上会自动删除并重新创建？

如果是这样，表副本是否是源表的100%精确复制品？

- CF_HoneyBadger

1

请查看以下与编程有关的内容：同时也可以查看https://dev59.com/w2kw5IYBdhLWcg3wdKbD#9870989 - pylover

2

如果您使用的是 MS SQL Server 2014 -> 请从顶部菜单中选择“Extras>Options>Designer”。 - Vityata

5个回答

95

只有在SQL Server Management Studio编程时它才知道如何仅删除并重新创建表。当然，有些情况下它会在不必要的情况下这样做，但也有一些情况是你在Management Studio中进行的编辑不会删除和重新创建表，因为它没有必要。问题在于列举所有情况并确定它们属于哪一方将会非常繁琐。这就是为什么我喜欢在查询窗口中使用ALTER TABLE，而不是隐藏其操作（而且说实话还有错误）的可视化设计工具 - 我知道会发生什么，并且我可以为仅删除和重新创建表的情况做好准备（这比SSMS经常对你这样做少）。

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

当然。如果您可以在不重新构建整个表的情况下自己编写脚本进行更改，那就更好了 - 考虑表大小为10TB，数据库记录非常频繁（考虑同步AG、更改跟踪、复制、编写不良触发器等），并且该表被频繁访问 - 这是潜在的灾难风险。如果您要进行的更改可以应用 ONLINE 提示或添加列并分批复制数据而不是使用“全部或无”的 GUI，则更好。

如果取消选中此框，表是否会自动删除并重新创建？

它可能会。有一长串的情况，结果取决于SSMS版本、SQL Server版本，有时还取决于版本。您可以通过勾选框并尝试在“无意义的数据库副本上”应用更改来进行检查，但是在我的看法中，实际使用 ALTER TABLE 脚本而不是点选式 GUI 是更好的方法。

如果是这样，表副本是否与源表完全相同？

是的，如果SSMS必须重建表，则在完成后它将是一个100％完全相同的副本（除了变更），但这可能会在下周三进行。该过程创建表的新版本，将所有数据复制到其中，然后删除旧表并将新表重命名。

- Aaron Bertrand

5

虽然这是一个非常好的回答，但我感觉它没有回答到提问者所提出的所有问题，而那些问题才是我真正感兴趣的。特别是“这样做有什么负面影响/可能存在的缺点吗？”和“如果有，表格副本是否完全与源表格相同？”。您是否有关于这些问题的信息？ - tfrascaroli

@tfrascaroli 让SSMS为您重建表格有什么缺点吗？当然有。如果您可以自己编写脚本而不必重建整个表格，那就更好了——考虑一下表格大小为10TB，数据库记录非常繁忙（考虑同步AG、更改跟踪、复制），并且表格被频繁访问——这可能是一场灾难的潜在因素。表格完成后将是一个100%精确的副本，但这可能要到下周三才能完成。 - Aaron Bertrand

12

只有以下情况下，SQL Server 才会删除并重新创建表：

添加新列
更改列的“允许为空”设置
更改表中列的顺序
更改列的数据类型

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

- Carol Baker West

9

你的列表不是详尽的。例如，可以添加/删除列上的“IDENTITY”属性。 - Aaron Bertrand

2

在字段末尾添加一个可为空的新列不需要重建表。 - PseudoToad

12

参考 - 关闭此选项可以帮助您避免重新创建表，但也可能导致更改丢失。例如，假设您在 SQL Server 2008 中启用了更改跟踪功能以跟踪对表的更改。当您执行导致表被重新创建的操作时，您会收到“症状”部分中提到的错误消息。但是，如果关闭此选项，则在重新创建表时将删除现有的更改跟踪信息。因此，Microsoft 建议您不要通过关闭该选项来解决此问题。

- user1499112

3

是的，这会带来一些负面影响：

如果您脚本化一个被此标志阻止的更改，您将得到以下脚本（我只是将Contact表中的ID列转换为自动编号的IDENTITY列，但该表具有依赖关系）。请注意以下可能发生的错误：

即使微软也警告这可能导致数据丢失（该注释是自动生成的）！
在一段时间内，外键不会被强制执行。
如果您在ssms中手动运行并且 ' EXEC('INSERT INTO '失败，然后让以下语句运行（它们默认情况下通过'go'分隔），则会插入0行，然后删除旧表。
如果这是一个大表，插入的运行时间可能很长，并且事务正在持有模式修改锁，因此会阻塞许多事情。

--

/* 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

- Andrew Hill

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Antoine Meltzheim · Accepted Answer

254

工具 --> 选项 --> 设计器节点 --> 取消选中 "防止需要重新创建表格的更改"。

- Antoine Meltzheim

3

阅读问题 - 这正是OP说不要费心的原因，因为他已经知道了。 - Pete Danes

2

这个问题（从它的第一个版本开始，我查看了历史记录）要求解释取消选中此框的任何缺点。你甚至没有试图回答这个问题。 - Ben Voigt

1

然而奇怪的是，这个答案得到了最多的投票。我想知道为什么有那么多糟糕的软件存在。 - Brett