2

I have four global temp tables (##A,##B,##C,##D) on which I want to perform a left join in SQL Server 2008. By global temp tables I mean I created them using a code like this:

SELECT *
INTO ##A

These four tables all have a column with same name 'key' and I want to perform join using this column. I am using this logic:

SELECT * 
INTO ##X
FROM ##A as a
LEFT JOIN ##B as b ON a.key = b.key
LEFT JOIN ##C as c ON a.key = c.key
LEFT JOIN ##D as d on a.key = d.key

When I execute this code I get the following error:

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

I have been stuck here and cannot seem to resolve it.

Any help/advice/suggestions much appreciated.

  • CAST all string datatypes to the same collation explicitely - in the final query's ON clause or (better) during temptable creation process. – Akina Nov 9 '18 at 11:22
  • Why are you using global temp tables instead of local ones? (This has nothing to do with your problem, I'm genuinely curious, because the only benefit I'm aware of is limiting concurrency, when that is a goal.) – Aaron Bertrand Nov 9 '18 at 12:49
  • Because I can then join them. Is it possible to join local temp tables as well? – T.H. Nov 9 '18 at 13:04
  • Yes, you can join local #temp tables (just not across multiple sessions). – Aaron Bertrand Nov 9 '18 at 13:04
  • Ah great, thanks. I have a small related question, when I do select* after joining the four tables it gives me 4 columns of 'key' in resultant table, do you have any idea as to how I can ensure that only the 'key' column from A table remains? – T.H. Nov 9 '18 at 13:08
0

While the accepted answer (i.e. "use COLLATE DATABASE_DEFAULT") appears to work here, and might even work in many other cases, it is not really addressing the issue and will actually be bad advice for some readers.

The underlying issue of this question is that:

  1. JOINs are being done on string columns, and
  2. more than one Collation is being used in those string columns being JOINed

Regardless of the type of table (permanent, local temporary, global temporary, table variable, etc), when mixing multiple strings in an operation (i.e. a predicate, concatenation, UNION, etc) the collation between them must either:

  1. be the same (for anything that cannot be coerced into another Collation), or
  2. be coercible into another Collation

Column collations are not coercible, whereas string literals and variables are. This question deals with JOINs between string columns, hence none of them are coercible, hence they all need to be the same collation, or you get the "Cannot resolve the collation conflict" error shown in the question.

Fixing this issue does require using the optional COLLATE clause, but I wouldn't recommend using DATABASE_DEFAULT in this case. The DATABASE_DEFAULT option simply uses the collation of the current database where the query is executing. That collation is not necessarily the collation of any of the columns in these four global temporary tables. The collations for the columns in the four base global temp tables (##A, ##B, ##C, and ##D) are what the collations were in the source tables when these global temp tables were created via SELECT * INTO.... This has nothing to do with the instance-level default collation that tempdb uses since these temp tables were not created via CREATE TABLE ##.... (where the string columns do not specify the COLLATE clause).

In cases like this one where database-level default collations are neither known nor being used, it is best to be intentional / explicit in the collation that you want to use. There are two collations mentioned in the error: Latin1_General_100_BIN and SQL_Latin1_General_CP1_CI_AS. Pick one of those for the COLLATE clause, depending on the intention of this query. Do these JOINs need to be case-insensitive, or do the JOINed columns need to be identical (i.e. a binary comparison)? Assuming you need the values to be identical, do the following:

SELECT * 
INTO ##X
FROM ##A as a
LEFT JOIN ##B as b ON a.key = b.key COLLATE Latin1_General_100_BIN
LEFT JOIN ##C as c ON a.key = c.key COLLATE Latin1_General_100_BIN
LEFT JOIN ##D as d on a.key = d.key COLLATE Latin1_General_100_BIN;

OR, depending on how the relationship between these tables works, you can even use different collations to better match different relationships between the different tables:

SELECT * 
INTO ##X
FROM ##A as a
LEFT JOIN ##B as b ON a.key = b.key COLLATE Latin1_General_100_BIN
LEFT JOIN ##C as c ON a.key = c.key COLLATE SQL_Latin1_General_CP1_CI_AS
LEFT JOIN ##D as d on a.key = d.key COLLATE Latin1_General_100_BIN;
1
SELECT * 
INTO ##X
FROM ##A as a
LEFT JOIN ##B as b ON a.key = b.key collate database_default
LEFT JOIN ##C as c ON a.key = c.key collate database_default
LEFT JOIN ##D as d on a.key = d.key collate database_default

Should eliminate the problem

But the underlying problem is that your (source) tables have different collations. This should be avoided.

This is a good source for some more information: Questions about sql server collations

  • Yes this solved the problem and the link you have added is very helpful too. Thanks. – T.H. Nov 9 '18 at 13:05
  • Also possible is that TempDB has a different collation than the user DB. – Michael Green Nov 10 '18 at 5:21
  • @MichaelGreen No, the tempdb collation is not involved here. Please see my answer for the explanation. – Solomon Rutzky Dec 7 '18 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.