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

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 differen开发者_运维知识库t 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?

---

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.

---

Okay not the best answer, but

You asked: "What are the real consequences of running with different collations" It can be a headache. The article you mentioned by Microsoft nails it on the head. In my personal experience I've come across that issue and it wasn't easy to avoid. Mismatched collations will pop up in unplanned places unless you test well.

You also asked "what might we do to avoid these problems if we do need to support multiple collations on the new servers?" Nothing comes to mind except to test like crazy.

I really wish you luck, it can be a common and hairy problem that I wouldn't wish on anyone.

---

My answer is not a good one too, but:

we have multiple subscriber servers synchronizing with our main database, and on some of them have a collation which is not the publisher's one. When launching replication, we keep on getting this "welcome message" telling us that, "as collations are not identical, the synch might not succeed".

Though this problem never occured, I guess there is a risk somewhere, and I think this risk could be linked to things like referential integrity and\or other constraints set on character fields.

Ah: and there is also this uppercase\lowercase issue in T-SQl instructions ... check this one here

@Michael and your DBA are right .... limit the risks, and use a unique collation.