Complications with SQL Server database having different collation than the server default?

Question

We are in the process of migrating databases off an old SQL Server 2k EE server with default collation "Latin1_General_CI_AS" onto new SQL Server 2005 & 2008 servers with default collation "SQL_Latin1_General_CP1_CI_AS". There are no international characters that would require Unicode that I know of, so the two codepages are almost the same for practical purposes.

The primary SQL Server DBA is adamant that every single database (most of which are built by 3rd-party apps) must be rebuilt with the new collation before he will migrate them.

I know that ever since SQL Server 2000 it's been possible to set individual databases to have a different collation than the default. But what are the real consequences of running with mixed collations? One article from Microsoft suggests complications with the shared tempdb, for example (but can it easily be avoided?).

And, perhaps more importantly, what might we do to avoid these problems if we do need to support multiple collations on the new servers?

Answer 1

The problem with different collations between server and db is as is mention before that temp tables will default be created with the server collation. That will make any comparisons on character fields between a temp table and a regular table fail. This can be avoided by the developers of the 3rd-party apps by using COLLATE database_default for character fields of temp tables.

create table #Tmp(Col1 nvarchar(50) COLLATE database_default)

I come from the "other" side. I'm not a DBA but a 3rd party software developer and I think that it is my responsibility to build my app to work in an environment where the collation is different between database and server. It is also my responsibility that my app will work with case sensitive collation.