Lesson Learned #97: Which is the server collation in Azure SQL Database?

Today, I worked on a case that our customer reported that importing a bacpac they got a duplicate object name, however, if they import the bacpac in a SQL Server OnPremise the process is completed without issues but Azure SQL Database they faced the following error:  Import fails 'Error SQL72014: Procedure ExampleFunction, Line 3 There is already an object named 'ExampleFunction' in the database'.

I'm asking why this difference and following I would like to share with you my lessons learned here:

• I changed the extension of the bacpac file to zip and I found that, indeed, we have two procedures with same name but one is written Examplefunction and another one is ExampleFunction.
• In the model.xml of this bacpac file I found that the collation of the database is LATIN1_GENERAL_BIN that is case sensitive and after asking to our customer we found that the server configuration iss indeed LATIN1_GENERAL_BIN so, this is the reason, that our customer is not getting any error having two procedures with same name but written different.
• In fact, running the query select * from sys.columns, I found that the system table sysschobjs that is managing the name is LATIN1_GENERAL_BIN.

• But, what is happening with Azure SQL Database?
  • First, I checked the collation of Azure SQL Database and I found that the collation of the database is indeed, LATIN1_GENERAL_BIN.
  • Second, I checked the collation of Azure SQL Server and I found that the collation of the server is SQL_Latin1_General_CP1_CI_AS that is case insensitive and for this error our customer is facing this error. Unfortunately, this definition is by design and nowadays it is not possible to change.

Enjoy!