> ... 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?][1]


  [1]: https://sqlquantumleap.com/2018/06/11/changing-the-collation-of-the-instance-and-all-columns-across-all-user-databases-what-could-possibly-go-wrong/