Preamble
I was modifying a column in SQL Server 2008 today, changing the datatype from something like currency(18,0) to (19,2).
I got the error "The changes you have made require the following tables to be dropped and re-created" from SQL Server.
Before you scramble to answer, please read the following:
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!
Actual question
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?
If so, is the table copy a 100% exact replica of the source table?
This question is related to
sql-server
sql-server-2008
database-design
sql-server-2005
Yes, there are negative effects from this:
If you script out a change blocked by this flag you get something like the script below (all i am turning the ID column in Contact into an autonumbered IDENTITY column, but the table has dependencies). Note potential errors that can occur while the following is running:
--
/* 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
Tools --> Options --> Designers node --> Uncheck " Prevent saving changes that require table recreation ".
SQL Server drops and recreates the tables only if you:
Using ALTER is safer, as in case the metadata is lost while you re-create the table, your data will be lost.
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.
Source: Stackoverflow.com