Quick post today…
Yesterday I was approached by a developer with something it has never occurred to me to try in 18 years of being a DBA.
“Can I write a query across two databases with different collations?”
My first thought took me back to SQL 7, when you had to reinstall SQL Server to change collation settings (IIRC). My second and immediate thought was “Quit being so old and look it up, Kevin.”
So I bounced over to The Google, then to an answer on StackExchange that I can’t find right now (of course, but I did upvote it yesterday) and found what I needed.
I have two test databases on my local SQL 2014 Dev instance, both with an Orders and OrderDetails table in them with no data. Both were the default collation so I changed one of them to CS instead of CI:
ALTER DATABASE [test2] COLLATE SQL_Latin1_General_CP1_CS_AS
I entered a test order in each DB manually, and in the “Note” column I changed the case of the word Five. Why Five? No idea…its just text, and that’s what my developer (Jared) was having to join on.
Select * from Test.dbo.orderdetails where note = 'five' Select * from test2.dbo.OrderDetails where Note = 'FIve'
OrderDetailID OrderID ItemQty Note ------------- ----------- ----------- --------- 1 1 5 Five (1 row(s) affected) OrderDetailID OrderID ItemQty Note ------------- ----------- ----------- --------- 1 2 5 FIve (1 row(s) affected)
Next up, write the Select statement the normal way to duplicate the error Jared was getting:
--fails Select * from Test.dbo.orderdetails A join test2.dbo.OrderDetails B on A.[note] = B.[Note]
Msg 468, Level 16, State 9, Line 11
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CS_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.
So…now to bring it in the correct way (and there is probably more than one correct syntax for this…I didn’t look):
--works Select * From Test.dbo.orderdetails A Join test2.dbo.OrderDetails B on A.[note] = Cast(B.[Note] as varchar(50)) COLLATE SQL_Latin1_General_CP1_CI_AS
OrderDetailID OrderID ItemQty Note OrderDetailID OrderID ItemQty Note --------------------------------------------------------------------- 1 1 5 Five 1 2 5 FIve (1 row(s) affected)
All I did here was tell the query to treat the Test2 Note field as Case Insensitive for this one query. Nothing changed in the source data itself, or the database settings/DDL.
It took me five times longer to write this up than to test it, since I already had some generic test databases ready to go. Same ones I use for testing my answers on MSDN, SSC and DBA Stack questions.
If this helped you, please share, like, re-post, etc. as you see fit. Or a thumbs up in a comment if you like.
Thanks for reading!
Thomas Franz says
– you can specify the COLLATION without CASTing it
– as every time an explicit or implicit (by defining a different collation) CAST would prevent index seeks, you could prevent this by adding and indexing a computed column (e.g. Note_CI), if you have often to join on this (or have tons of orders)
Excellent points…I figured there were multiple ways to do it. I just ran with the first one I found, as it was an ad hoc request. In this case a change to the database such as a new column would not have been a good solution, but I can other cases where it would. Thanks!
Bob van Ierssel says
maybe this helps, works for me all the time:
on A.[note] = B.[Note] collate database_default