Multiple Collations

Question

Post reply

Multiple Collations

Steve Jones - SSC Editor

SSC Guru

Points: 737868
More actions
April 25, 2025 at 12:00 am

#4574862

Comments posted to this topic are about the item Multiple Collations

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Bert De Haes Hall of Fame Points: 3261 More actions · Answer 1

Why then does this works in SQL Server 2022:

select *

from sys.objects

where name = ('sysrscols' collate French_CI_AS) collate French_CI_AS;

and also

select *

from sys.objects

where name = ('sysrscols' collate Latvian_CS_AI_KS) collate French_CI_AS;

This reply was modified 2 weeks, 5 days ago by Bert De Haes.

chris.mackenzie 149 Say Hey Kid Points: 690 More actions · Answer 2

Or even here where the collations are different:

select *

from sys.objects

where name = ('sysrscols' collate French_CI_AS) collate French_CS_AS;

At the end of most articles is a small blurb called a person's signature which exists to provide information about how to get in touch with the person posting, including their email address, phone number, address, or where they're located. Signatures have become the graffiti of computers. People put song lyrics, pictures, philosophical quotes, even advertisements in them. (Note, however, that advertising in your signature will more often than provoke negative responses until you take it out.)

jschmidt 17654 SSCommitted Points: 1684 More actions · Answer 3

I played the chatGPT game with this one and after a few iterations, it eventually comes down to unclear documentation. I'm not sure I understand it completely, but this is the "Why does WHERE ColumnA = ('abc' COLLATE French_CI_AS) COLLATE French_CS_AS sometimes fail?

Here’s what happens:

'abc' COLLATE French_CI_AS — okay

Then COLLATE French_CS_AS is applied

Now the literal has a final explicit collation of French_CS_AS

If ColumnA has a different collation, say Latin1_General_CI_AS, and both sides have explicit collations, SQL Server will not coerce either one, and throws:

Cannot resolve collation conflict for equal to operation.

This is what the docs are warning you about. When both sides of a comparison have explicit collations that differ, SQL Server does not know which to convert, and raises an error.

Bert De Haes Hall of Fame Points: 3261 More actions · Answer 4

But I tried with different tables/columns and collations, and it never failed ...

sys.objects.name is (on my server) Latin1_General_BIN, and "where name = ('sysrscols' collate Latvian_CS_AI_KS) collate French_CI_AS" does not fail ...

Steve Jones - SSC Editor SSC Guru Points: 737868 More actions · Answer 5

I think the docs are wrong. I was doing a few things here and didn't test the explicit phrase in the docs, which appears to be wrong.

My own tests with collations that likely don't go together bears this out. I'll submit a PR to correct this once I figure out what the rule is. In the meantime, I'll change the answer and award back points.

2025-04_0164