Conflist between collations

Question

Post reply

Conflist between collations

SQL Guy 1

SSCoach

Points: 15974
More actions
August 13, 2014 at 4:11 pm

#310721

Hi all.
My server collation is SQL_Latin1_General_CP1_CI_AS.
From sys.databases, all databases including tempdb, master all are SQL_Latin1_General_CP1_CI_AS too.
Now, in my script I join 2 sys tables with a #temp table:
create table #object_types
(RowNum tinyint,
TypeDesc varchar(40))
insert into #object_types
values (1,'USER_TABLE'),
(2,'VIEW'),
(3,'SQL_STORED_PROCEDURE'),
(4,'SQL_SCALAR_FUNCTION'),
(5,'SQL_TABLE_VALUED_FUNCTION'),
(6,'SQL_INLINE_TABLE_VALUED_FUNCTION')
select SchemaName = s.name,
ObjectName = o.name,
Type = o.type_desc
from sys.schemas s
join sys.objects o on s.schema_id = o.schema_id
join #object_types t on o.type_desc = t.TypeDesc
drop table #object_types
Msg 468, Level 16, State 9, Line 18
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS_KS_WS" in the equal to operation.
Why? All my collations including tempdb are SQL_Latin1_General_CP1_CI_AS.
Where did it get Latin1_General_CI_AS_KS_WS from ?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 1

If you run the following code...

sp_help 'sys.objects'

... and look at the "Collation" column has in it, you'll see why the problem exists. You'll need to use the COLLATE clause to change the collation in your query.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL Guy 1 SSCoach Points: 15974 More actions · Answer 2

Hmm... indeed. I also found that all type, description columns in sys... views have Latin1_General_CI_AS_KS_WS collation. I don't understand why Microsoft did it, to complicate our queries.

Steve Jones - SSC Editor SSC Guru Points: 734552 More actions · Answer 3

Microsoft didn't do it that I can see. I've checked my US default 2008 and 2012 instances. Both of them have the objects at the same collation as the master database and server objects.

I suspect someone installed your server incorrectly and changed the db collation.

SQL Guy 1 SSCoach Points: 15974 More actions · Answer 4

I ran this query on about 10 randomly chosen servers in our shop, and I got 218 records in a result set.

SELECT s.name ,

o.name ,

o.type_desc,

c.name ,

c.collation_name

FROM sys.schemas s

JOIN sys.all_objects o ON s.schema_id = o.schema_id

JOIN sys.all_columns c ON o.object_id = c.object_id

WHERE c.collation_name = 'Latin1_General_CI_AS_KS_WS'

ORDER BY 1,2

I will also run it at home tonight to prove my point.

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

Ugh, you are right. I was checking a few random columns.

Sys.objects has the name in the default, but type in the other collation. That is crazy. I'll try to find out why.

Steve Jones - SSC Editor SSC Guru Points: 734552 More actions · Answer 6

I did find this: http://blog.bugrapostaci.com/tag/latin1_general_ci_as_ks_ws/. That makes some sense, but I'm not sure why we wouldn't set the default for servers to be _KS_WS as well? It shouldn't matter in English, but it could for Unicode items.

Strange.

Steve Jones - SSC Editor SSC Guru Points: 734552 More actions · Answer 7

FYI, here's the explanation from MS:

Some of the columns that contain pre-defined strings (like types, system description, constants) are always fixed to a specific collation – Latin1_General_CI_AS_KS_WS. This is irrespective of instance/database collation. The reason is that this is system metadata and basically these strings are treated case-insensitive (like keywords so always Latin).
Other columns in system tables that contain user metadata like object names, column names, index names, login names take the instance or database collation. The columns are collated to proper collation at the time of installation of SQL Server in case of instance collation & at the time of creation of database in case of database collation.
This has always been like this (at least use of the Windows collation for these columns). So not sure what is failing & why? I suspect the problem the user was having was collation conflict between columns. Repro script will help.