How to change database or server collation

Moving databases across a distributed environment very often results in collation conflict. For instance you have two sql server boxes – one in China and another one in Californina. No wonder two servers use differrent collations lets say Chinese_PRC_CI_AI and Latin1_General_CI_AS. And most probably databases hosted on each server have its server collation. One day you copy database from China to your US server and restore it. Everything goes well unless you don’t use tempdb.

Tempdb has always the same collation as the sql server instance it belongs to so if you somehow sort or match character values in tempdb (join, order by clause etc) your query will fail. You can easily reproduce this collation scenario using the next query (assume your server collation is differrent from Arabic_100_CS_AI_KS_WS)