My friend has faced this problem against some queries so the cause of this problem as described in above title related to the Collation.

So He has a table with columns with different collation at Column level for example a Table called "Students" with "FirstName" and "LastName" columns and each of these columns has a different collation so the "FirstName with SQL_Latin1_General_CP1_CI_AI collation " and "LastName with Arabic collation" and when he write the following query he got an error: 

SELECT FirstName + ' ' +LastName
FROM dbo.Students

The solution for this problem is easy so rewrite the above query as following:

SELECT FirstName + ' ' + LastName  
COLLATE SQL_Latin1_General_CP1_CI_AI LastName
FROM dbo.Students 

As well as if you try to write a query as the following:

SELECT * FROM dbo.Students 
WHERE FirstName = LastName

You will get the following error:
Msg 468, Level 16, State 9, Line 1 Cannot resolve the collation conflict between "Arabic_CI_AS" and "SQL_Latin1_General_CP1_CI_AI" in the equal to operation
To fix it, rewrite the above query as following:

SELECT * FROM dbo.Students 
WHERE FirstName = LastName COLLATE SQL_Latin1_General_CP1_CI_AI