Cannot resolve the collation conflict

Consider this SQL Server error message:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

Suppose you install SQL Server using Latin1_General_CI_AS collation, then restore a database (from a different instance) that uses SQL_Latin1_General_CP1_CI_AS. Later, you create a temp table and then try and do a join from the database to the temp table on a char/varchar field ... you will get an error message similar to the message shown above.

You cannot join char/varchar fields that use different collations. Changing the collation of the tempdb is quite difficult ... typically requiring a complete reinstall of SQL Server. However, the COLLATE database_default option provides a relatively simple solution to the problem. Here is an example:

create table #tbl (
  fld1 varchar(20) collate database_default null
)

However, you should only use this technique when you are populating the temp table with the same data you are joining to. Blindly using COLLATE database_default can lead to some unexpected results.