Recently, I have came across a situation where I found there is different SQL Collation is set for System databases and user databases on a sql instance in production.
Like: Danish_Norwegian_CI_AS for master, tempdb,model and msdb. Danish_Norwegian_CI_AI for USER_DB's.
Would there be any problem with this type of collation settings?
This question is definitely a duplicate of SQL Server default collation vs database with different collation - potential problems?
However, as that question was purely generic and this question specifies the two collations, I can at least clarify one of the issues raised in that other question:
do you see any potential data loss?
In this case there is no potential data loss due to the only difference being a sensitivity (accent sensitivity), which affects sorting and comparisons, but not which character set is being used to store VARCHAR data.
Though, depending on how one defines "data loss", it should be pointed out (at least for the sake of thoroughness) that there is a possibility that a temp table is being used that contains a string column that is not being given an explicit collation when the temp table is created (either Danish_Norwegian_CI_AI or DATABASE_DEFAULT, there is no appreciable difference between them here), and is thus using Danish_Norwegian_CI_AS. That could lead to a query classifying two values as being different if they only differ in accents (well, and yes, also case, width, and kana-type), whereas using Danish_Norwegian_CI_AI would classify them as being equal. This can return unexpected results, or cause an error. But, neither case is a character conversion, which is what I was initially addressing in terms of "data loss".
