Altering Databases - Collation Tab

Article
06/05/2016

Changes the collation that is used by the database.

To open this window, on the File menu, choose Database, choose Alter, and then choose the Collation tab.

Before you change the collation, you have to select the Single user option on the Options tab.

Changing the Collation of a Dynamics NAV Database

If you change the database collation, then the collation of objects in the database is changed except for tables that have the LinkedObject property set to Yes. You must manually re-create these objects. For example, you can script them in SQL Server Management Studio.

If you change the collation from a case-sensitive to a case-insensitive collation or from an accent-sensitive to an accent-insensitive collation, then duplicates can occur in the primary keys of the tables. Duplicates can be caused by the values of the character data stored in the primary keys. If duplicates occur, then you receive an error message and the database collation change is stopped. We recommend that you do not change these attributes of a collation.

Note

Changing the collation can be a lengthy process that depends on the size of the database and the number of companies in the database. The system tables and all user table indexes that contain character data must be rebuilt.

The Language drop-down list displays the friendly name of the language, not the full Windows collation name. For some languages, there are multiple collations that sort characters differently. For example, the Windows collation languages include multiple Scandinavian languages, some of which sort Aa after Z, Æ, Ø, and some of which sort Aa after A and before B. If you upgrade from Microsoft Dynamics NAV 2009 to Microsoft Dynamics NAV 2018, you upgrade the database to the Windows collations. If you used SQL collation in earlier versions of Dynamics NAV, then after you upgrade, verify that the Windows collation sorts characters in the way that you expect.

If you set the Validate Collation check box, then collation languages that run with a different non-Unicode code page from your system non-Unicode code page are filtered out of the Language drop-down list. An example scenario of when you might want to choose a collation language that has a different code page from your system code page is if you want to prepare a Japanese database on a Danish computer.

You cannot change the collation directly in the current database. To change the collation, you must create a new database that uses the correct collation, and then export the data from the old database and import it to the new database. For more information, see How to: Alter Databases

Share via

Altering Databases - Collation Tab

Changing the Collation of a Dynamics NAV Database

See Also

Additional resources