SQL Server default collation vs database with different collation - potential problems?

Question

I'm struggling with the design of new database that has to use a different collation (Y Collation) than the SQL Server Instance default collation (X Collation). I'm looking for potential problems.

Temp Tables by default will be created with X Collation so any joins on text data types will fail unless I create temp table with Y collations on columns.

What about SQL Server string functions: REPLACE, SUBSTRING, etc.? Will they always be returned in X collation? What about when I use REPLACE(...) COLLATE Y? Do you see any potential data loss or maybe any different problems?

Answer 1

... new DB that has to be in a different collation (Y Collation) than the SQL Server Instance default collation (X Collation).

Why does the new database need to be in a different collation?

Temp Tables by default will be created with X Collation so any joins on text data types will fail unless I create temp table with Y collations on cols.

Correct. Though you do not need to use COLLATE DATABASE_DEFAULT, and in some cases that can be entirely incorrect / bad. The requirement is that either A) the collation is the same for string columns being joined, or used in concatenation / UNION, etc, or B) you specify the COLLATE clause in the predicate or expression to override one or more of the columns.

Meaning, if you know that your column has a collation of Z and your database has a default collation of Y, then create the temp table specifying COLLATE Z for that column. Keep in mind that table variables use the database's default collation, not the instance's collation. And, contained databases are yet another set of rules.

What about SQL Server string functions: REPLACE, SUBSTRING, etc.? Will they always be returned in X collation?

Built-in functions usually return a string in the database's default collation, unless you pass in a column or an expression using the COLLATE clause.

do you see any potential data loss?

It depends on what datatype(s) you are using and what collations you are using. NVARCHAR / NCHAR / NTEXT / literals prefixed with an uppercase "N" are Unicode-only (UTF-16) and so there is no potential data loss if switching collations on those. VARCHAR / CHAR / TEXT / literals not prefixed with an uppercase "N" are 8-bit encodings, the particular encoding being determined by the code page association with the collation being used. Here there is potential data loss if switching collations to one that uses a different code page. But even that is not a guarantee of data loss since data loss only occurs when there are characters in the data that are not available in the new code page. (technically speaking, you can also experience data loss when mixing various combinations of single-byte character sets, double-byte character sets, UTF-8, and UTF-16, depending on which direction you are going in, but this is not something most people run into -- though more people likely will as more folks start using the new UTF-8 collations starting in SQL Server 2019)

or maybe any different problems?

Just keep in mind that:

1. Instance-level collation controls:
   • variable name / cursor name / GOTO label resolution. It won't matter what the database-level collation is.
   • instance-level metadata: login names, server names, server role names, server-level objects, database names, etc.
   • default collation for:
     • newly created databases (unless the CREATE DATABASE statement included the COLLATE clause)
     • tempdb metadata (names of temp object / constraints / indexes / defaults / permanent tables / etc)
     • columns in temp tables and permanent tables in tempdb
2. Database-level collation controls:
   • database-level metadata: user names, schema names, database role names, schema-bound object names, etc.
   • default collation for:
     • columns in table variables, permanent tables, TVF results
   • string literals
   • string variables (the contents of the variable, not the name)
   • return value of scalar UDFs

---

For a more thorough list of what collations do at the various levels and what to look out for when changing them, please see the following post of mine:

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

Answer 2

There is no easy way to do this.

As you have mentioned it will create issue with shared TEMPDB and by default tables will be created in Collation X. to overcome this if you want to create a table in tempdb you can use below syntax.

    CREATE TABLE #TempTable(MyColumn NVARCHAR(50) COLLATE DATABASE_DEFAULT)

Collation can be case sensitive too. While accessing columns you might have to use exact column names (with exact Uppercase characters/Lowercase characters).

Another suggestion that I can think of is, you have to do thorough testing. You have to cover every corner in your testing.