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

序言

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

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

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

我已经知道在工具►选项►设计器►表和数据库设计器►取消选中“防止保存需要重新创建表的更改”。 .．.所以不要这样回答!

实际的问题

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

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

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

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

当前回答

是的，这也有负面影响:

如果你用脚本删除一个被这个标志阻止的更改，你会得到如下脚本所示的东西(所有我将Contact中的ID列转换为一个自动编号的IDENTITY列，但是这个表有依赖关系)。注意在运行以下程序时可能发生的潜在错误:

Even microsoft warns that this may cause data loss (that comment is auto-generated)! for a period of time, foreign keys are not enforced. if you manually run this in ssms and the ' EXEC('INSERT INTO ' fails, and you let the following statements run (which they do by default, as they are split by 'go') then you will insert 0 rows, then drop the old table. if this is a big table, the runtime of the insert can be large, and the transaction is holding a schema modification lock, so blocks many things.

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

2017-10-15 23:31:51

其他回答

工具—>选项—>设计器节点—>取消勾选“防止保存需要重新创建表的更改”。

2014-06-19 16:21:30

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

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

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

2012-09-04 20:47:50

是的，这也有负面影响:

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

2017-10-15 23:31:51

Reference - Turning off this option can help you avoid re-creating a table, it can also lead to changes being lost. For example, suppose that you enable the Change Tracking feature in SQL Server 2008 to track changes to the table. When you perform an operation that causes the table to be re-created, you receive the error message that is mentioned in the "Symptoms" section. However, if you turn off this option, the existing change tracking information is deleted when the table is re-created. Therefore,Microsoft recommend that you do not work around this problem by turning off the option.

2012-08-04 11:27:10

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

当然，在某些情况下，它会在不需要的情况下这样做，但也有一些情况下，您在Management Studio中所做的编辑不会删除并重新创建，因为它不需要这样做。

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

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

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

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

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

它可能。场景有一个洗衣清单，结果取决于SSMS的版本，SQL Server的版本，有时是版本。您可以通过勾选复选框，并尝试先将更改应用到没有意义的数据库副本上，但是以我的观点，使用实际的ALTER TABLE脚本而不是点式单击GUI才是正确的方法。

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

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

2012-08-03 19:58:10

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

推荐文章

最新文章

标签