CODEWITHSUNDEEP
sqlsql-serverinternationalizationcollationcodepages
Gennady Vanin Геннадий Ванин
Gennady Vanin Геннадий Ванин

Reputation: 10384

Why doesn't explicit COLLATE override database collation?

I am on SQL Server 2008 R2 dev, server default collation is Cyrillic_General_CI_AS

Executing in SSMS
SELECT 'éÉâÂàÀëËçæà' COLLATE Latin1_General_CS_AS
or 

SELECT 'éÉâÂàÀëËçæà' COLLATE Latin1_General_CI_AI

outputs

Why?

Upvotes: 2

Views: 1231

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239714

Those character literals in your queries are first converted to varchar strings under whatever collation the database is set for, and then your collation cast takes effect.

If you want to pass such character literals and ensure all characters are faithfully represented, it's better to pass them as nvarchar literals:

create database CollTest collate Cyrillic_General_CI_AS
go
use CollTest
go
SELECT 'éÉâÂàÀëËçæà' COLLATE Latin1_General_CS_AS   
SELECT 'éÉâÂàÀëËçæà' COLLATE Latin1_General_CI_AI
go
SELECT N'éÉâÂàÀëËçæà' COLLATE Latin1_General_CS_AS   
SELECT N'éÉâÂàÀëËçæà' COLLATE Latin1_General_CI_AI
go

Output:

-----------
eEaAaAeEc?a

(1 row(s) affected)


-----------
eEaAaAeEc?a

(1 row(s) affected)


-----------
éÉâÂàÀëËçæà

(1 row(s) affected)


-----------
éÉâÂàÀëËçæà

(1 row(s) affected)

Upvotes: 6

Abe Miessler
Abe Miessler

Reputation: 85056

According to this character table Cyrillic_General_CI_AS doesn't contain the æ chacter. This would explain why you see a ? in it's place if you are truly using the defaults instead of what you have in your select statements.

Upvotes: 1

Related Questions