4

I am trying to join two tables, from separate databases, in a query. They have different collation types. The main DB has the collation: SQL_Latin1_General_CP1_CS_AS

The DB that I want to join to has the collation: SQL_Latin1_General_CP1_CI_AS

I get this error when I try and run without collation (obviously):

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

I am a stranger to collation, so I do not know what to do. If I remove the aspects of the second DB from the query, it runs instantaneously. Once I bring the join into play, the query runs for an hour+ with no results.

Please let me know if you need more context.

Dom Vito
  • 467
  • 6
  • 32
  • Can you show the two differents query you are using? – Ludovic Feltz Apr 10 '18 at 15:01
  • Do you want to join in a case sensitive way or not? – EzLo Apr 10 '18 at 15:02
  • 2
    Changing collation of the column will make the query non-SARGable; thus any indexes you have will be ignored on that column. This is most likely why your query becomes so slow. – Larnu Apr 10 '18 at 15:04
  • @Larnu after doing a little googling, that's what I am finding. Is there a good way to get around that? Unfortunately, not really in a position where I can alter the tables. – Dom Vito Apr 10 '18 at 15:11
  • @EzequielLópezPetrucci i do not care about case, for what it's worth! – Dom Vito Apr 10 '18 at 15:11
  • Try use join syntax like this ,`tableA.Column collate DATABASE_DEFAULT = tableB.Column` – Ven Apr 10 '18 at 15:14
  • You can try inserting one of your tables to a temporary table with the changed collation and indexing it. – EzLo Apr 10 '18 at 15:20
  • @EzequielLópezPetrucci sounds workable.. could you link to an example on how to index it? unfamiliar with indexing myself. – Dom Vito Apr 10 '18 at 15:23

1 Answers1

2

As discussed in the comments, chnaging the collation of a column, in the query, makes the statement non-SARGable. This is why your seeing a significant performance lose, as you can't use your indexes.

If you need to keep the collation different on the 2 databases, then one method would be to add a PERSISTED computed column. Then you can add an index onto that column instead. This will keep the SARGability.

If it's the non-CaseSensitivity you want to keep, then you need to create the computed column within your Case Sensitive database. I don't know the name of your column, but yuour statement for your computed column would be something like:

ALTER TABLE YourTable ADD YourColumn_CI AS YourColumn COLLATE SQL_Latin1_General_CP1_CI_AS PERSISTED;

Then you can create indexes on that new column as well, and you should receive a performance benefit.

Edit: As a very simple example index:

CREATE INDEX YourColumn_CI_IX ON YourTable (YourColumn_CI ASC);
Larnu
  • 61,056
  • 10
  • 27
  • 50
  • I guess I do not understand how to create the index within the query without altering the tables. Do I need to utilize a temporary table? – Dom Vito Apr 10 '18 at 15:58
  • No, no need to use a temporary table at all. What part of my answer don't you understand here? Indexing, however, isn't something that can simply be taught by in an answer on SO; or it's comments. – Larnu Apr 10 '18 at 16:00