SSDT database projects CASE changes

Just a quick post today, to highlight one of the quirks of SSDT database projects.

Usually when you hit publish, or publish through your automated pipeline, or generate a script, or do whatever else you do to push the project out onto a server the process is pretty simple. There’s some sort of comparison engine that runs under the hood, and it does some technical wizardry to detect any differences between your project and the database you’re deploying to. It generates a script based on those changes, and runs that script against the database to bring it in line with the project. Simple enough, right?

Mostly, yes. But there are some cases where it isn’t that simple. I’ve blogged before about how you need to write any check constraints in a specific way, and today I’m going to look at another of these quirks.

The scenario

Recently in my job we were looking at tidying up the cases of some description fields in our dimensions. One of the procedures that generates the dimension data was generating a description field as ‘YES’ and ‘NO’, and we felt that ‘Yes’ and ‘No’ were better options. This was all being generated through a case statement (CASE WHEN Column = 1 THEN 'YES' WHEN Column = 0 THEN 'NO' END) so we thought it would be an easy fix to just change the YES to a Yes etc. We were wrong.

When we came to deploy, we found that the changes weren’t deploying to any of our environments. As they were part of a larger change set, we were really puzzled about why it was just these procedures that weren’t deploying, until we realised that it was because the only difference between the project version and the database version of these procedures was the case of the labels. I did some looking into it, and it turns out this is expected behaviour. I had a look around the publish options, and couldn’t find any ‘detect changes in case’ type option, and the internet suggested the only way to make the publish process work the way we wanted was to update the collation of the database to be case sensitive.

This was definitely a pain, and the answer in the end was to add a comment to the procedures to trick the comparison engine into thinking they’d changed, but the more I thought about it the more it made sense. By setting the database to be case insensitive, we had told SQL Server that we didn’t care about the case of anything in that database. Why, in that case, would the comparison engine waste time altering a proc when the only difference was something we had called out as unimportant?

More generally, I think this speaks to a principal of doing the right thing in the right place. If your database is case insensitive then don’t try to do case sensitive stuff in there. Instead, worry about the case in whatever layer you have that does care about the case of your labels. And if that layer is your database then make it explicit by making the database case sensitive.