SSDT generated script drops and re-adds default and check constraints every time - by deadlydog

Status : 


Sign in
to vote
ID 793433 Comments
Status Active Workarounds
Type Bug Repros 4
Opened 7/10/2013 3:22:06 PM
Access Restriction Public


I'm not sure if this belongs in the Visual Studio feedback or the Sql Server feedback, so move it to the appropriate place if neccessary.

For more information see the MSDN post:

Essentially the problem is that when publishing to a database from a Visual Studio 2012 .sqlproj project (using the SSDT tools), it detects superficial differences between the database project and the database's default constraints, check constraints, and computed columns, so it adds lines to the generated .sql script to make them all match.  The problem is that when the script runs, it doesn't actually update the database schema to make it match the database project's schema, so these alter statements get included in every single publish script that you make, which adds up to a lot of wasted time during the deployments.
Sign in to post a comment.
Posted by Cody Konior on 6/15/2016 at 2:41 AM
Confirming this same behaviour still exists in SSDT 2016 for VS 2015. However using the workaround of altering my BETWEEN statements to use >= and <= fixed the issue for me.

I will note however that this was a silent killer for a long time. I did not realise that this bug was happening and thought that legitimately every time I had to deploy an update to a project with 30 million rows, it really had to recreate the schema. This ended up wasted a lot of time over hundreds of incremental deployments.
Posted by William Bosacker on 3/3/2014 at 3:42 PM
I just started having this problem, as we didn't have a need for any check constraints until now. I added about 10 or so CHECK constraints to a few different tables and all of them use the "IN" keyword, which for some stupid reason SQL Server changes to multiple "=" statements. The following:

CHECK ([ZipClassificationCode] IN ('M', 'P', 'U'))

gets changed to:

CHECK (([ZipClassificationCode]='U' OR [ZipClassificationCode]='P' OR [ZipClassificationCode]='M'))

inside of SQL Server, and thus causes this failure. There is no way that I am changing the "IN" to multiple"=" expressions. You need to fix this ridiculous issue.
Posted by deadlydog on 12/13/2013 at 8:01 AM
Just adding another common superficial difference that we often see; our table statements will have (user), but it wants (user_name()).

e.g. We have:
[CreatedByUser] [nvarchar] (256) NOT NULL DEFAULT (user),

but it wants this:
[CreatedByUser] [nvarchar] (256) NOT NULL DEFAULT (user_name()),

So as mentioned in my workaround, we change our table definitions' source code to use (user_name()).
Posted by deadlydog on 12/4/2013 at 9:05 AM
I'll also add that the work around I mention needs to be performed periodically; as schema changes are made to the database over time, more of these superficial differences appear. Until a fix is implemented for this, I've setup a monthly reminder for myself to go in and correct all of the superficial schema changes that get put into all of our publish scripts.
Posted by deadlydog on 8/9/2013 at 10:02 AM
Thanks for the comment. The work around you give is only good where the column type is money with a default of 0. This problem happens with many other scenarios as well though. Often in check constraints subtle differences like spaces, casing, or more/fewer parenthesis cause problems too.

Here's an example. We had this:

ALTER TABLE [dbo].[iQclerk_PayOuts] ADD CONSTRAINT [CH1_POT_ReconciledDetailsAreValid] CHECK ([Reconciled] = 0 and [DateReconciled] is null and [ReconcilerEmployeeID] is null or [Reconciled] = 1 and ((not([DateReconciled] is null))) and ((not([ReconcilerEmployeeID] is null))));

but it wanted this:

ALTER TABLE [dbo].[iQclerk_PayOuts] ADD CONSTRAINT [CH1_POT_ReconciledDetailsAreValid] CHECK ([Reconciled]=(0) AND [DateReconciled] IS NULL AND [ReconcilerEmployeeID] IS NULL OR [Reconciled]=(1) AND NOT [DateReconciled] IS NULL AND NOT [ReconcilerEmployeeID] IS NULL);

and another example. We had this:

ALTER TABLE [dbo].[iQclerk_CashOutsAndTotals] ADD CONSTRAINT [CH1_COAT_PaymentTypeValid] CHECK (((not([PaymentTypeID] = 100))) and (((not([PaymentTypeID] = 6))) and ((not([PaymentTypeID] = 0)))));

but it wanted this:

ALTER TABLE [dbo].[iQclerk_CashOutsAndTotals] ADD CONSTRAINT [CH1_COAT_PaymentTypeValid] CHECK (NOT [PaymentTypeID]=(100) AND (NOT [PaymentTypeID]=(6) AND NOT [PaymentTypeID]=(0)));

So in the end our work around was to do a schema compare between our database project and the database we publish to, and then for each default and constraint that was getting needlessly dropped and recreated every time, we changed our database project files to match what was in the database. This often made our files a bit less readable, but now that they are identical it doesn't identify any differences between them and try and drop and recreate them.
Posted by Microsoft on 8/8/2013 at 4:54 PM
Thank you for the feedback. I am still able to reproduce it with our latest version. We won't be able to fix this right now. In the meantime, the work-around is to change the default value in the table to ($0.0000), which is how it is interpreted on the back end. That will avoid the constant redeploy of the default constraint.
Posted by dejann on 7/15/2013 at 3:39 PM
I had this issue in VS 2010

I've upgraded to VS 2012 update 2 with June 2013 SSDT and the issue has been fixed.

Posted by Macy [MSFT] on 7/11/2013 at 1:53 AM
Thanks for your feedback.

We are rerouting this issue to the appropriate group within the Visual Studio Product Team for triage and resolution. These specialized experts will follow-up with your issue.
Posted by Macy [MSFT] on 7/10/2013 at 3:51 PM
Thank you for your feedback, we are currently reviewing the issue you have submitted. If this issue is urgent, please contact support directly(