Home Dashboard Directory Help
Search

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


Status: 

Active


13
0
Sign in
to vote
Type: Bug
ID: 793433
Opened: 7/10/2013 3:22:06 PM
Access Restriction: Public
2
Workaround(s)
view
1
User(s) can reproduce this bug

Description

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: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/e3f4fd52-5467-4ab9-90d4-95f1b78bb2a4/ssdt-generated-script-drops-and-readds-default-and-check-constraints-every-time

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.
Details
Sign in to post a comment.
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 dejan1 on 7/15/2013 at 3:39 PM
Hi,
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.
http://blogs.msdn.com/b/ssdt/archive/2013/06/24/announcing-sql-server-data-tools-june-2013.aspx


Regards
Dejan
Posted by Microsoft 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 Microsoft 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(http://support.microsoft.com)
Sign in to post a workaround.
Posted by deadlydog on 8/9/2013 at 10:04 AM
Do a schema compare between the database project and the database you publish to, and then for each default and constraint that is getting needlessly dropped and recreated every time, change your database project files to match what is in the database. This may make your files a bit less readable, but it will make them identical so that it doesn't identify any differences between them and try and drop and recreate them needlessly.
Posted by Microsoft on 8/8/2013 at 4:55 PM
You can use ($0.0000) as the default value for money types, rather than ($0), since that is how SQL Server interprets it. This will cause SSDT to see them as the same and thus avoid the redeploy of the default constraint.