SQL Server collation vs. NAV DB Collation

emerikspemeriksp Member Posts: 10
edited 2017-10-04 in SQL General
Hi,
My customer requests recommendations for collation on SQL Server and NAV database. I have been told (years ago), that it is good practice to ensure, that SQL Server collation and NAV DB Collation match. Failing to do so could potentially cause trouble, allegedly because in some cases (temporary tables, perhaps?) system databases (e.g. tempdb, master, which inherit the server collation) are used.
I don't remember who told me this, and I can't find anything describing the issue. So I thought, I would ask in here:
Does anyone know, which issues (if any) might be caused by having different collations on the NAV database and the SQL Server on which, the NAV DB is installed?
References to official documentation (e.g. from MS) would be extremely appreciated, if such exists.

Thanks
Emerik

Answers

  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    Hi,

    Generally none I can think of. Well, maybe, if you have database users, and some non-ascii characters used in their names, that would be the one thing where different collations (master databse collation and NAV database collation) would be mixed. And event then database users are linked to SQL Server logins by a generated GUIDs, so maybe some diplay issue possible.

    NAV does not use tempdb explicitely, SQL sever can potentially use the temdb internally if the query results in a large data set which has to be spilled over to the tempdb, but even then the SQL server quarantees consistency.

    Nope, nothing rings a bell.

    Slawek
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • JuhlJuhl Member Posts: 724
    NAV doesn’t use the DB collation anymore, it’s set on a table by table basis to support multi tenancy.

    The Cronus database is Case Sensitive, but often customer want that changed. You alter the table in dev. environment, and this changes all tables, but the DB is still CS if you look in SSMS.
    Follow me on my blog juhl.blog
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    On a table by table basis? Where can you specify a collation on a single table?

    The collation is still specified at the database level when you created it.

    When you change collation on existing database then new collation must be enforced on each text column in the each table, with ALTER TABLE ... ALTER COLUMN ... command. This is because column collation is determined once, when a table is created, and altering the collation at the database level does not affect existing tables. It used to be like that in NAV pre-2013 and it is now, and the reason is that it is the SQL Server specific thing, not the NAV specific thing.
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • JuhlJuhl Member Posts: 724
    Yes, but NAV doesn’t change collation on dB anymore, just on tables.
    I think we are on the same page :-)

    But it shouldnt be change in SSMS.
    Follow me on my blog juhl.blog
  • kenniekennie Member Posts: 34
    NAV does not use tempdb explicitely, SQL sever can potentially use the temdb internally if the query results in a large data set which has to be spilled over to the tempdb, but even then the SQL server quarantees consistency.

    Small info: The NST utilizes Multiple Active Result Sets (MARS), which uses version store in tempdb.
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    kennie wrote: »
    Small info: The NST utilizes Multiple Active Result Sets (MARS), which uses version store in tempdb.

    Can you shed some more light on this please? Specifically on MARS enabled connection using the version store.

    So far I thought that MARS is only about enabling applications to run multiple queries/batches within the same connections and enabling the app to receive the records from one query before results from another are fully received. That's it. Where the version store comes into the equation?

    Slawek

    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • kenniekennie Member Posts: 34
    Hi Slawek,

    The MARS technology uses the version store.

    See https://technet.microsoft.com/en-us/library/ms175492(v=sql.105).aspx
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    Thank you @kennie
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
Sign In or Register to comment.