by Michael S. Kaplan, published on 2007/10/10 11:01 -04:00, original URI: http://blogs.msdn.com/b/michkap/archive/2007/10/10/5391882.aspx
Steve asks via the Contact link:
Michael,
Great blog and I scan it for SQL related stuff for our SQL newsletters at sqlservercentral.com.
Recently I came across and issue and since you're the smartest guy I know for this stuff, I thought I'd ask. I figured this might make an interesting blog posting as well.
I was writing a cross database query from "new" to "old" to compare some data transformations. I got this error:
Server: Msg 468, Level 16, State 9, Line 1 Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
I know how to solve it, but I had a question. What's the difference between these collations? One is the default for SQL Server in the UK and one in the US.
We restored a US database onto a server built in the UK. Then we created a new db in the UK, and migrated data across.
Anyway, thanks for listening and I understand if you don't have time to reply.
Steve
My post SQL Server: compatibility collations vs. Window collations is probably the best answer here to explain the difference between these two collations. It is just that given how awful the SQL compatibility collations are for text outside of English, they are pretty much only the default in the US (otherwise SQL Compatibility collations are a bit too retro because Unicode and SQL Collations have nothing to do with each other).
There is plenty of battling to try to get that US default to be changed, but there is plenty of inertia given the performance difference of between the tables that support all character in Unicode and the ones that support just the small subset. But, maybe one day....
This post brought to you by £ (U+00a3, a.k.a. POUND SIGN)