When generating a deployment script from SSDT it always generates code that drops the foreign key constraints & indexes then creates again those constraints and indexes. How to remove the code that drops and recreates those objects when generating deployment script? Any options in SSDT?

  • 1
    is it always dropping and rebuilding, or only when a table is altered? Beause when a table is altered the process is to create a new table, select into the new one, and drop the old table; this means the keys and indexes have to be re-established? – Andrew Hill Feb 11 '15 at 4:11
  • Does your table schema contain persisted computed columns? – Cody Konior Aug 14 '16 at 5:44

It is normally because there is a difference between how they are written and how sql stores them.

As a one time thing to a compare back from the db to the project and you should see some difference on the keys, apply them and it should stop doing it.

  • Especially an issue for persisted computed columns which require an additional null in the definition. – Cody Konior Aug 14 '16 at 5:44
  • I come across this most often with check constraints using in they show up as a diff unless rewritten to expand out to or – Martin Smith Feb 11 '17 at 18:12

As the previous answer stated, it will drop and recreate objects in many situations where it does not need to. Your best bet is updating either the database or the source control definition for the objects to be in sync with each other, and not rely on the SSDT reconciliation tool, which makes very bad choices.

For example, if there is a difference in a constraint name, for example, between source control and the database environment, you could rename it in the database environment to be consistent with SC, or rename it in SC to be consistent with the DB. Otherwise the SSDT will want to drop and recreate it because it always takes the path that will work in all situations, not the path of least resistance.

I've seen it drop and recreate a table just to change the column definition on one column from VARCHAR(50) to VARCHAR(100).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.