Collation can be set at all different levels in SQL, from the server, down to the column level. And it's a pain when it does't match up. Fortunately, there is a fast workaround for T-SQL queries.
SELECT a.employeeid, a.name, b.employeeid, b.name
FROM employee a, employeedetails b
WHERE a.employeeid = b.employeeid
If the collation on employeeid in table a does not match the collation for employeeid in table b, there will be a nasty error, along the lines of "Cannot resolve collation conflict for equal to operation"
Easy enough to deal with - tell SQL to use what *should* be the default for the database(s) in question. This worked when I was dealing with two databases with totally different collations on the same server - the default server collation didn't trickle down to a database imported from another, older, server.
SELECT a.employeeid, a.name, b.employeeid, b.name
FROM employee a, employeedetails b
WHERE a.employeeid COLLATE DATABASE_DEFAULT = b.employeeid COLLATE DATABASE_DEFAULT
This is a lot simpler to implement than any solution that involves trying to re-collate one of the columns to the other table/database/column's collation. It's vastly more generic.
