Return to Answer

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

What comes to SQL Server text functions Replace, Substring etc. They will be always returned in X collation?

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 or maybe any different problems?

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)

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

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?

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

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)

... new DB that has to be in different collation (Y Collation) that 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 comes to SQL Server text functions Replace, Substring etc. They will be always returned in X collation?

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 or maybe any different problems?

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.

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?

... new DB that has to be in different collation (Y Collation) that 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 comes to SQL Server text functions Replace, Substring etc. They will be always returned in X collation?

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 or maybe any different problems?

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)

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?