Case sensitive dacpac for coding standards

In our project we have coding standards for SQL objects. But when a code review made us update casing we noticed the dacpac did not contain the changes after a schema compare. What happend?

Seems that our sql server databases are case in-sensitive by default. That is a good thing since everything keeps working, even with broken coding standards. I’ve never seen a case sensitive database and wonder if they even exist.

The dacpac is configured to be case in-sensitive too. But that is about to change. Why? Because making the dacpac case sensitive was the solution. On the properties of the SqlProject there is a Database Settings button. Click it and set the Database collation to something with CS in it. (CS = Case Sensitive) Now the casing updates are synced to our SqlProject and compiled into the dacpac.

sqlproject_case_sensitive

Make sure to disable the deployment of database settings. That could break the software. You can exclude this by specifying the commandline option

/p:ScriptDatabaseOptions=false

About erictummers

Working in a DevOps team is the best thing that happened to me. I like challenges and sharing the solutions with others. On my blog I’ll mostly post about my work, but expect an occasional home project, productivity tip and tooling review.
This entry was posted in Development and tagged , , , , . Bookmark the permalink.

2 Responses to Case sensitive dacpac for coding standards

  1. Scott says:

    We recently ran into the same problem: a stored procedure that was updated with only casing differences wasn’t deployed. I found your article while looking for a fix. This looks like a change that might have other consequences. It’s been almost 5 years now. How has this change worked out for you?

    • erictummers says:

      We stil use this solution today. Since we don’t apply database properties the setting case sensitive isn’t applied to the database. So during deployment the difference in procedure name is noticed and applied, while the database remains case insensitive.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.