Is_System_Named

Is_System_Named

Issue in  Database Deployment in Agile Process 

When you migrate the DDL from one server to another server in agile development then the major problem you will face because of constraints name. As all comparison tool have same problem; it will give the differences if you have only constraints name different even for same constraint. That will suggest you to delete and recreate the constraint with the source name you have. So suppose if a primary key is getting created without specifying the name then system will give one unique name to the primary key. So while comparing two database will show you differences because the name of the primary key is different even though we have same primary key (on same table , same column). Then the tool will suggest you to delete this primary key and recreate with source primary key name. As this primary key is referring many foreign key you have to first delete all the foreign key then delete the primary key and recreate the primary key and then back create all the foreign keys.

Drop and recreate all Foreign Key

Drop and recreate all Foreign Key 

         Truncate table will fail if table is referenced by child tables. So its big challenge for a developer when he want to reload table which has huge data. Lets take one example, we have a table with 10 millions of records and I have to reload this table,i.e. delete all the existing data and reload the data. If I perform the delete operation it may take more than 20 minutes as table is big it has 30 columns, and the second problem is fast growing log. The best way to clean the data is to truncate the table this will not take more than 2 or 3 sec. But table will not allow you to truncate as it is referenced by other table , even the child table don't have data it will throw error. 

  So I have written below query that will generate script for the table to drop all the foreign key constraint and recreate constraint. So I used to execute the first part that drop all the FK constraint and then truncate table, then run the create constraint script and then reload the table , its more better if you recreate constraint after data load.

 You can find this script on my MSDN contribution page.

Generate script to drop and recreate all referential constraints

http://gallery.technet.microsoft.com/scriptcenter/Generate-script-to-drop-edd9e9c1

Caution: Please generate create constraint SQL first and save and keep it safe then generate drop constraints.