Azure SQL Database Collation

Question

My databases on Azure SQL Database have different collection than the default Collection of the existing Instance. I need to find a way to change this default instance collection to the same one of my databases. These databases are constantly consulting sys schema and manipulating the result using queries like the example below:

SELECT * From mySchema.myTable
Where myColumn in (select name from sys.tables)

ERROR: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation

I already found an old answer here which says that this change is not possible. Has it been updated since?
https://social.msdn.microsoft.com/Forums/en-US/8d181fe4-3b67-44aa-b854-a3f55373fee7/windows-azure-sql-database-collation?forum=ssdsgetstarted

Answer 1

Hi

Article here - this can only be set when the instance is created and cannot be changed afterwards:

https://learn.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-server-collation?view=sql-server-ver16#setting-the-server-collation-in-managed-instance

Hope this helps,

Thanks

Michael Durkan

• If the reply was helpful please upvote and/or accept as answer as this helps others in the community with similar questions. Thanks!

Answer 2

If you cannot change the collation of the tables, then try changing the query:

. . .  
where myColumn collate SQL_Latin1_General_CP1_CI_AS in (select name from sys.tables)  

Answer 3

Please consider using the workaround provided on this blog post.

Answer 4

By default, on SQL Azure SQL Database, the catalog collation is SQL_Latin1_General_CP1_CI_AS. There does not seem to be a way to change the catalog collation.

One way is to create a new database:

   CREATE DATABASE NewDB COLLATE Latin1_General_CI_AS WITH CATALOG_COLLATION = database_default  

This will give you the same collation for data and metadata.

If you don't want to do this and copy data over, you can use the COLLATE clause as suggested by Viorel, although this variation may be better:

   WHERE myColumn IN (SELECT name COLLATE database_default FROM sys.,tables)