Collation can be defined at column level in MS SQL Server. An annoying error that comes up when using two columns that have different collations defined.

For example:

SELECT A.USER1, A.USER2, B.ROLENAME

FROM USERS A, ROLES B

WHERE A.ROLEID = B.ROLEID

A.ROLEID and B.ROLEID both have different collations and it upon executing above query it will generate an error: “cannot resolve collation conflict.

To resolve this error add COLLATE DATABASE_DEFAULT on both side of equal operator:

SELECT A.USER1, A.USER2, B.ROLENAME

FROM USERS A, ROLES B

WHERE A.ROLEID COLLATE DATABASE_DEFAULT = B.ROLEID COLLATE DATABASE_DEFAULT

Resolved.