SQL Collation in MS Dynamics Navision

SQL Collation in MS Dynamics Navision

 

It refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

SQL Server includes a large set of collations for handling the language and regional differences that come with supporting users and applications in different parts of the world.

For, more information about collation please refer.

 

Collation and Unicode Support

Windows Collation Name

 

Latin1_General_100_CS_AS, which means that the database engine will enforce both case-sensitivity and accent-sensitivity when sorting and comparing character data.

The first part indicates the (language and) code page. The later part CS AS etc. indicates compare/sort rules.

Collation uses the Latin1 General dictionary sorting rules and maps to code page 1252. It is a version _100 collation, and is case-sensitive (CS) and accent-sensitive (AS).

 

Change Collation in Database

To Change collation Go to Database -> File -> Database -> Alter Database

Case Sensitivity(_CS): 

Distinguishes between uppercase and lowercase letters. 

If A & a, B & b etc. are not equal, then it is case sensitive.

The ASCII value of A is 65, while a is 97. The ASCII value of B is 66 and b is 98

CI specifies case-insensitive, CS specifies case-sensitive.

Accent sensitivity(_AS):

Distinguishes between accented and unaccented characters

If a and á, o and ó are not equal, then it is accent-sensitive.

The ASCII value of a is 97 and áis 225. The ASCII value of o is 111 and ó is 243.

AI specifies accent-insensitive, AS specifies accent-sensitive.

Kana Sensitivity(_KS):

Distinguishes between the two types of Japanese kana characters

When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive

KS specifies kanatype-sensitive

Width sensitivity(_WS):

Distinguishes between full-width and half-width characters

When a single-byte character (half-width) and the same character when represented as a

double-byte character (full-width) are treated differently, then it is width sensitive. WS specifies width-sensitive.

MS Dynamics Navision shows error message, if we are trying to update collation without updating Validate Collation check mark.

It is recommended to use below steps for new collation parameter.
Change Database Collation

1.     Export the data from old Nav db.

2.     Create new Nav db with correct collation parameter. 

3.     Import the old Nav db to the new db.

4.     Synchronize Schema

5.     Restart Nav Server Instance.

 

Thanks & Best Regards,

Pankaj Gupta