Hi,

I have published a SQL database on SQL Azure. The database's collation is SQL_Latin1_General_CP850_CI_AS, obviously server's collation is SQL_Latin1_General_CP1_CI_AS.

In the database I have some views and stored procedures that query sys views or use OBJECT_NAME function.

OBJECT_NAME function should returns a Name of object in the same collation of current database (see remarks in MSDN definition). This works in SQL 2005 and SQL 2014, but in SQL Azure the function returns the name in the server collation (SQL_Latin1_General_CP1_CI_AS). This causes errors in my views/procedures when I compare the return of OBJECT_NAME function with values saved in my fields.

There is the same issue in sys database views (sys.identity_columns, sys.tables, sys.columns, etc.) and also in INFORMATION_SCHEMA views (I have checked INFORMATION_SCHEMA.COLUMNS). IN SQL 2005 and SQL 2014, this views return varchar fields with database collation. In SQL Azure this fields have server collation.

These are bugs?

Is there a way I can avoid this issue without forcing collate in every comparison that I make in the views/procedures that rely on these system views/functions?

Thanks,

Davide

Metadata collation is fixed to SQL_Latin1_General_CP1_CI_AS in Azure SQL Database. This is similar to the fixed metadata collation used when you create a partially contained database in SQL Server (CONTAINMENT = PARTIAL). So the COLLATE clause in the DATABASE DDL only affects the data collation not the names of objects, columns & such. This also affects tempdb / temporary tables since they will inherit the master collation which is fixed to SQL_Latin1_General_CP1_CI_AS.

As a result, when you perform comparison between a column in user table & a column in system table you need to convert to a common collation typically to avoid conflicts. One way to do this is by using the COLLATE catalog_default clause like:

where sys.tables.name = mytable.name COLLATE catalog_default

We are looking at adding support in SQL Database to create servers/databases like in SQL Server where the metadata collation is same as the data collation. Hope this helps.