What collation to use?

Hi all!

 Im in the progress to create a new database for a company that is located all around europe (Sweden, Denmark, Norway, France, Netherland, Belgium and Austria) does anyone know what collation I should use in order to be able to enter the correct characters in Navision? They all will share one database and would be able in theory to look at each others data.

No Data
  • We had the same question as we have a setup much like yours. Our only additional requirement is that we need to be able to support double byte character sets also. Here are there recommendations we received by our consultant:

    As promised hereby the conclusions based on my test of windows collations and sql collation on central databases with data input using different OS (e.g. double-byte)


    Recommended collation today by Microsoft is Windows collation because this is never than SQL collation


    However using Windows collation together with non-unicode programs is resulting in problems.


    This is probably due to the handling of non-unicode in windows collations which are handled in the same way as Unicode characters where using a SQL collation, SQL Server defines different comparison semantics for non-Unicode data. For more info refer to Microsoft: http://support.microsoft.com/kb/322112


    Using a windows collation on an SQL server where non-unicode data is stored and input using different OS will result in following errors:

    • Text files exports can change special characters
    • NAV databasetest or backup of company (which automatically also executes as database test) will result in error message “There is an error in the database structure …….” And NAV backup will NOT be possible


    • Collation for the SQL database for a global NAV system should be a SQL Collation e.g. “Scandinavian dictionary order, case-sensitive, for use with the 850 (Multilingual) character set.”
    • NAV collation when creating database should be a SQL Collation e.g. “Scandinavian dictionary sort order, code page 850, case-sensitive, accent-sensitive (59)”
    • For optimal performance make sure that the SQL collation and NAV collations are identical.
    • The selected collation can be case-sensitive and accent-sensitive if wished

    Which collation:

    • The Scandinavia (850) collation includes all standard West-European and US characters and in addition the special Scandinavian characters.
      • One known issue with using above SQL collation is that NAV will NOT distinguish between V and W (meaning that in code fields should NOT be used equal names except for V/W)
    • In general when having double-byte characters or other special country characters requiring special OS in same central database these characters should be AVOIDED in all CODE fields.

    I hope you can use this, otherwise the consultant (Kim M. Larsen) is also a member here, and might be able to answer additional questions you might have.