Collation is in rough and ready terms the order that SQL Server uses for sorting or comparing textual data. An example of what a SQL Server instance might use as it’s collation order could be LATIN_CI_AS (or latin, case insensitive, accent sensitive)

When you install SQL Server, you set the collation order for the master (and all other systems databases) No really you DID! It looked alot like a next button. One huge ramification of what you chose is that the temporary database (tempdb to it’s friends) is a system database and inherits it’s collation order from the master.

If you subsequently choose another collation order for one of your user databases, you open yourself up to a rather big gotcha! Should you create a temporary table in a stored procedure within this new database, SQL Server rather (un)cleverly chooses to inherit the collation order of tempdb for this temporary table and NOT the collation order of the user database. If you then compare (or join) this temp table to a real one SQL Server will inform you that it “Cannot resolve collation conflict on = operation” or some such.

To solve this, it makes sense to stipulate the collation of columns that are to be compared in temporary tables when you create them using the COLLATE keyword.