TIL: Joining across databases with different collations

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'

Results:

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]

Result:

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

Result:

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!

Kevin3NF

(stalk me on Twitter)