How to deal with collations

Sadly, the need to inspect a database to find orphaned records is not uncommon. One typical query you might write would probably look like this one:

select *
from table_one
where my_key not in
(
  select my_key
  from table_two
)

Every now and then the keys you need to compare are strings with different collations, so a query like the one above would yield error messages. To resolve the problem you can cast one of the field:

select *
from table_one
where my_key not in
(
  select cast (my_key as ${type}) collate ${collation}
  from table_two
)

Obviously, ${type} and ${collation} are just placeholders. If you are using SQL Server you can execute the Transact-SQL sp_help procedure to get the informations you need for the cast:

exec sp_help table_one

Of course, numeric keys would solve the problem at its roots. But... have you ever heard of natural keys and legacy databases?