When we set up our SQL Server 2005 server, we did some research on the collation settings and we decided on using the Windows collation (Latin1_General_CI_AI) because of the sort issues using Unicode data and non-Unicode data and also because MS is going to deprecate the SQL Collations. http://support.microsoft.com/kb/322112

However, when we migrated some of our SQL Server 2000 databases to SQL Server 2005, we did experience some issues because of the different collation settings between the TempDB and the migrated databases. We wrote a script to go through and alter the database, tables, and columns to change the collation settings of them to the Windows collation and everything works fine.