I found that even for a completely new AX installation, the AX model et baseline databases have discordant columns collations even for columns belonging a same table. We followed the recommendation about collations for AX:
All Microsoft Dynamics AX databases must use the same SQL collation. These databases include the business database, model store database, Microsoft SQL Server Reporting Services database, and Microsoft SQL Server Analysis Services database.
One of the collation is the same as the server’s default collation which is Latin1_general_ci_as. The collation property of every AX database is also latin1_general_ci_as. But for AX Baseline and AX Model database, some tables have different columns with different collation. For example with the table dbo.ModelSecurityPolicyTBD, the column ELEMENTNAME have the old SQL collation and all other columns are the same as the database which match also the default server’s collation.
For this other table most of the columns are in the old SQL collation, and only one match database and server collation:
The query below list all tables with columns for which collations are not all the same.
-- Query that finds collation differences into AX databases
dense_rank() over(order by collation_Name) as ordreCollation
, dense_rank() over(partition by collation_Name order by table_schema, table_name) as ordreTable
Where collation_name is not null
and OBJECTPROPERTYEX(object_id(table_schema+'.'+table_name), 'isUserTable')=1
Order by ordreCollation desc
As a DBA I know that having several collation into a database is running into problems. Query needs to have the collate clause if there is column comparisons or join doesn’t match columns for which collations are not the same. The collate clause may prevent also index use, making queries less efficient.
I wonder if these differences are just overlooked or are properly handled by AX??
A sentence need to be rephrased in the previous post:
Query needs to have the collate clause if there is column comparisons or join between columns for which collations are not the same.
Business Applications communities