Friday, November 30, 2012

AX 2012 - SQL Server Collation


There is a difference in collation setting between SQL Server default collation setting used for system databases (Master, Model, Msdb, TempDB) and the Microsoft Dynamics AX database . Latin1_General_CI_AS_KS_WS  v.s. Latin1_General_CI_AS

 

Database Name
Collation name
AX2012PROD
Latin1_General_CI_AS
master
SQL_Latin1_General_CP1_CI_AS
model
SQL_Latin1_General_CP1_CI_AS
msdb
SQL_Latin1_General_CP1_CI_AS
tempdb
SQL_Latin1_General_CP1_CI_AS
 
 

It is recommended that the collations of user-defined databases match the collation of master or model. Otherwise, collation conflicts can occur that might prevent code from executing. For example, when a stored procedure joins one table to a temporary table, SQL Server might end the batch and return a collation conflict error if the collations of the user-defined database and the model database are different. This occurs because temporary tables are created in TempDB, which bases its collation on that of model.

 

If you experience collation conflict errors, consider one of the following solutions:

·        Export the data from the user database and import it into new tables that have the same collation as the master and model databases.

·        Rebuild the system databases to use a collation that matches the user database collation. For more information about how to rebuild the system databases, see Rebuilding System Databases (http://technet.microsoft.com/en-us/library/dd207003.aspx).

 

More information can be found here:

http://technet.microsoft.com/en-us/library/bb402915.aspx
Windows Collation Name (Transact-SQL)


Here you can find a good post that explain the difference between collation Latin1_General_CI_AS (United Kingdom) respect SQL_Latin1_General_CP1_CI_AS (United States).
It’s better to use Latin1_General_CI_AS collation because is a Windows collation and the rules around sorting unicode and non-unicode data are the same.

Difference between collation SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS

 

8 comments:

Alex P said...

Changing collation on AX 2012 R2 is not supported anymore by Microsoft. I was running into ciphering issue regarding stored procedure while trying to change the database collation.

Tommy Skaue said...

How huge a problem is this for AX2012? Does it have any performance implications?

Denis Macchinetti said...

Hi Tommy

As long you use the same collation for SQL Server default and Dynamics AX database there is no issue with using the Windows collation Latin1_General_CI_AS instead of the SQL Server collation SQL_Latin1_General_CP1_CI_AS but don’t mix them.

Also, read the article below.
On the conclusion you can read : ...Mixing the collations at column, database or instance level could impact on your performance...

http://www.olcot.co.uk/sql-blogs/revised-difference-between-collation-sql_latin1_general_cp1_ci_as-and-latin1_general_ci_as

Enjoy!

erp system said...

Thank you for describing a problem with SQL Server collation in Microsoft Dynamics AX and showing the difference in collation setting between SQL Server default collation setting used for system databases. It was interesting for me!

microsoft dynamics partners said...


Hi guys! Thank you that you wrote a lot interesting information about Microsoft Dynamics AX and showing how to describing a problem with SQL Server! It’s very helping me, because I'm a beginner :) and it was very interesting for me.

Dick de Jong said...

@Denis, i see it is very old post, but i have question on what you said:

As long you use the same collation for SQL Server default and Dynamics AX database there is no issue with using the Windows collation Latin1_General_CI_AS instead of the SQL Server collation SQL_Latin1_General_CP1_CI_AS but don’t mix them.

What if :

- my server default collation is Latin1_General_CI_AS
- my user db is Latin1_General_CI_AS
- my master, model tempdb are SQL_Latin_General_CP1_CI_AS

Is that a problem? should i convert my system db's to Latin1_General_CI-AS?

Dick

Denis Macchinetti said...

Hi Dick

Take a look also here. http://www.sqlservercentral.com/blogs/rocks/2012/01/09/revised-difference-between-collation-sql_latin1_general_cp1_ci_as-and-latin1_general_ci_as/

There are difference between the two collections.
AX 2012 as you know use a lot the TempDB tables in combination with the regular tables, especially with DIXF.

So, if you want to avoid performance issues I suggest to align the Collaction between the AX Databases and the System Databases

Denis

Dick de Jong said...

thanks for your recommendation, clear to me. Will align those ...

Dick