Got Collation conflict, How to avoid this?

The best way to avoid SQL Server's collation conflict issue is that avoid have different collations in your database schema. 

•  If your server and database have the same collation, you will never see the conflict collation issue.
•  if all string columns in a database use the same collation, you will see less collation conflict unless you do cross-database query.  You don't need explicit set the collation for string collations, they will pick up the database's collation by default.
• Keep in mind, changing existing database's collation is allowed, but it wouldn't changing existing table's collation, so they will have change of mix of different collations.
• I believe the common case of collation conflicts happens when your tempdb collation (which is your server collation) is different with your current database' collation.  Objects created on tempdb will automaticlly pick the server collation, not the current database collation.  The best way to solve this is this article:https://sqlskills.com/BLOGS/KIMBERLY/post/Changing-Database-Collation-and-dealing-with-TempDB-Objects.aspx

• When you hit collation conflict,  you can always use explicit collate clause to resolve the conflict.
• If you have any other concern or suggestion,  please send me detail about your issue.  You can also vote for letting SQL Server solve this issue at https://connect.microsoft.com/SQLServer/feedback/Validation.aspx?FeedbackID=324910