Why is SSDT Always Rebuilding My Constraints?
Let me begin by saying that I’m a big fan of SSDT. It’s free, it works with all flavours of Visual Studio, the team do their very best to keep up-to-date with features that are released with increasing regularity by Microsfot in both Azure and SQl Server on-prem. I’ve met the team a few times, and they’re genuinely keen to engage with the users of SSDT in how it can be better, and how it can be extensible.
So, SSDT is a great tool. I said great, but not perfect. It has it's limitations, the same as any tool. Sometimes SSDT does "things" that you would not expect it to do. These "Known Unknowns" are sometimes a pain to deal with, and one of the reason why people shy away from deploying in an automated manner. It's worth noting that getting to the bottom of "why" SSDT does things, it's limitations and how to anticipate and workaround those issues will allow you to adopt the tool to aid in deployments.
Today I am going to focus on an issue that we came across recently: A constraint was constantly being rebuilt on a database despite the fact that what was in source matched exactly what was on the target. Let's recreate the example: I have a database with a table called ConstraintSyntax, based on an Orders table. On this table there is a constraint on the ShippedBy column to only allow values of the shipping clients* in the constraint list to be added (the solution can be downloaded on GitHub.)
So here is the constraint in the solution:
And the constraint is yet to exist on my local dev instance:
So let's publish it from Visual Studio.
As you can see the script includes creating the constraint on the database, using the exact same syntax as in the solution. If we check in SSMS, it is added. All is good.
Now, let's suppose we make another minor change and wish to publish that to our local instance. You would expect only that change to be in the script, right?
Of course, if this was right, then this would be a pretty pointless post! In fact, the constraint is created again.
So just what is going on?
The first thing to do is to run a "Schema Compare..." within Visual Studio against out local database. Theoretically there should be no changes at all. Especially as there's one table with one constraint.
Come to find out there is a difference: and when we compare the difference it is purely syntax: whereas in the solution we use "IN" to define the accepted values in the constraint, the syntax on the instance is using "OR = ". Despite the fact that there is no drift in the model, this syntactic discrepancy is the sole reason that this constraint is being re-built. This task takes ~400 milliseconds on a 400,000 row table. And even so this is local, this will happen in all your environments. So on live deployment to production, this is bad, even if it does take less time.
The way to resolve this is to make the solution the same as the target, and mercifully SSDT has an option in "Schema Compare..." to migrate the changes over.
Now when you come to deploy the constraint will not be rebuilt. Tidy!
Ultimately, this issue is an easy one to fix, and reduces time a script takes to execute. As mentioned at the beginning of this post, it's important to identify the actions that SSDT includes within a deploy script that are not expected, why SSDT is doing this, and resolving with a workaround. And most importantly raise a Connect ticket detailing the issue to get it fixed.
*and let's not get into how this is lousy and just focus on the issue I'm demonstrating.