SQL – Cannot resolve collation conflict for equal to operation

When the sql collation of strings is not equal but need to me compared we need to explicitly define the collation. The most common way is the using of the default database collation as in the example:

WHERE Table1.Name COLLATE DATABASE_DEFAULT=Table2.Name COLLATE DATABASE_DEFAULT

The collation change can be performed on tables joining, 'where' clausesq as part of stored procedures and functions, as well as database Default collation change.