Database Collation Inconsistencies

Hi All

I've a situation where my production environment database is in collation SQL_Latin1_General_CP1_CI_AS while the remaining databases (DEV,QAS, and ACC) are in Latin1_General_100_CI_AI_SC. Can you let me know if this could be creating issues ? We will soon migrate to a new servers and maybe we could take extra steps to standardize the collations across the environment.

Thanks in advance!

Solution

Hi Luis,


You should have the same collation in all your data bases, the main problem you will have is the inconsistencies in your apps search between production and the rest of the environments.


For example on production the search will not ignore the accentuation while in the other environments it will.


You can check here on how to change the collation on the current server or the new one.


Best regards.


Solution

So this is outside of the scope of OutSystems Platform, this is purely database related and therefore the most reliable information would be on the communities around that topic.


Nonetheless, reading this and this I think you'll have a mismatch on accented characters.

SQL_Latin1_General_CP1_CI_AS means it's accent sensitive (a and â are different) while Latin1_General_100_CI_AI_SC is accent insensitive (a and â are considered the same)

(Obrigado!) Thanks Ricardo and Cristiana for your replies  the "AS/AI" tips the problem indeed.

Hi Luis,

You can tell some differences between both collations from their names:

 - CI stands for Case Insensitive;

 - AS stands for Accent Sensitive;

 - AI stands for Accent Insensitive;

What this means is that comparing characters with accents will be treated differently: with a collation that ends in AI, comparing "ú" with "u" would return True, and in a collation that ends in AS, this would return False. 

The same logic would apply if you had two sets of collations with CI and CS, but for casing instead ("u" vs "U", for instance).

You should always try to have your data residing in the same collations.

Thanks for the extra Info Afonso!

Afonso Carvalho wrote:

Hi Luis,

You can tell some differences between both collations from their names:

 - CI stands for Case Insensitive;

 - AS stands for Accent Sensitive;

 - AI stands for Accent Insensitive;

What this means is that comparing characters with accents will be treated differently: with a collation that ends in AI, comparing "ú" with "u" would return True, and in a collation that ends in AS, this would return False. 

The same logic would apply if you had two sets of collations with CI and CS, but for casing instead ("u" vs "U", for instance).

You should always try to have your data residing in the same collations.