How to fix Collation conflict error

During comparison or join of columns, collation conflict error occurs in two cases if collation of one column does not match with collation of another column.

This can be generated by below script:

CREATE TABLE TestTab

(PrimaryKey int PRIMARY KEY,

CharCol char(10) COLLATE French_CI_AS,

CharCol2 char(10) COLLATE greek_ci_as

)

INSERT INTO TestTab VALUES (1, 'abc', 'abc')

SELECT * FROM TestTab WHERE CharCol = CharCol2

This will return the collation conflict error. “Cannot resolve the collation conflict between "Greek_CI_AS" and "French_CI_AS" in the equal to operation”. But if we change the select statement as below then it would run successfully.

SELECT * FROM TestTab WHERE CharCol = CharCol2 COLLATE Albanian_CI_AI

Here note that explicit collation (Albanian_CI_AI) is not one of any column. But after that it completed successfully. Here we haven’t matched the collation of two columns but implemented the third rule of collation precedence.