SQL Server collation vs. NAV DB Collation

emeriksp · 2017-10-03

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

Slawek_Guzek · 2017-10-03

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

Juhl · 2017-10-03

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.

Slawek_Guzek · 2017-10-03

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.

Juhl · 2017-10-04

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.

kennie · 2017-10-10

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_Guzek · 2017-10-10

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

kennie · 2017-10-11

Hi Slawek,

The MARS technology uses the version store.

See https://technet.microsoft.com/en-us/library/ms175492(v=sql.105).aspx

Slawek_Guzek · 2017-10-11

Thank you @kennie

SQL Server collation vs. NAV DB Collation

Answers

Categories