Prevent saving changes that require the table to be re-created negative effects

Question

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

User · Answer

Tools --  Options --  Designers node --  Uncheck   Prevent saving changes that require table recreation

User · Answer

SQL Server drops and recreates the tables only if you    Add a new column  Change the Allow Nulls setting for a column  Change the column order in the table  Change the column data type   Using ALTER is safer  as in case the metadata is lost while you re-create the table  your data will be lost

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] "Prevent saving changes that require the table to be re-created" negative effects

Examples related to sql-server

Examples related to sql-server-2008

Examples related to database-design

Examples related to sql-server-2005