• Skip to main content
  • Skip to primary sidebar

DallasDBAs.com

Explaining SQL Server in plain english

  • Services
    • SQL Server Health Check
  • Pocket DBA®
  • Blog
    • Speaking
  • Testimonials
  • Contact
  • About

TIL: Joining across databases with different collations

May 16, 2017 by Kevin3NF 3 Comments

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)

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to email a link to a friend (Opens in new window)
  • Click to print (Opens in new window)

Related

Filed Under: SQL, TIL, TSQL

About Kevin3NF

Reader Interactions

Comments

  1. Thomas Franz says

    May 22, 2017 at 2:42 am

    Two points:
    – 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)

    Reply
    • Kevin3NF says

      May 22, 2017 at 6:44 am

      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!

      Reply
  2. Bob van Ierssel says

    May 24, 2017 at 1:35 am

    Hi Kevin,
    maybe this helps, works for me all the time:

    on A.[note] = B.[Note] collate database_default

    Greetings Bob

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Search

Sign up for blogs, DBA availability and more!


Categories

  • Accidental DBA
  • Apprentice
  • Azure
  • backup
  • backup
  • Beginner
  • Career
  • Configuration
  • Cycling
  • Dallas DBAs
  • Deployment
  • Emergency
  • Encryption
  • EntryLevel
  • Fries
  • Goals
  • HADR
  • HealthCheck
  • Index
  • Install
  • IRL
  • Management
  • Migration
  • MySQL
  • Oracle
  • PASS
  • Performance
  • Personal
  • Pluralsight
  • Podcast
  • PowerShell
  • red gate
  • Restore
  • Security
  • Speaking
  • SQL
  • sql 2005 log shipping suspect
  • sql injection
  • SQLSaturday
  • SSIS
  • SSMS
  • Summit
  • TIL
  • Tools
  • Training
  • Troubleshooting
  • TSQL
  • TSQL2sday
  • Uncategorized
  • Upgrade
  • vendor code modify
  • video
  • vNext

Copyright © 2023 · Genesis Sample on Genesis Framework · WordPress · Log in

Privacy Policy