12 July 2006 11:19 tonyrogerson

Collations in SQL Server, examples and restrictions

Probably the first you meet this thug is through this error message:

Msg 468, Level 16, State 9, Line 1

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

Its at this point you scratch your head and think what on earth? You may well have just installed a new copy of SQL Server as part of a server upgrade or more probably a result of restoring a production database into your development SQL Server (quick tip: always check the collations of the master databases on the two installs to make sure they are the same, that way you won't end up installing the wrong collation, and note SQL_Latin1_ is different from Latin1_, check the collation using the DATABASEPROPERTYEX Collation property, an example is show below  ).

Lets get some stuff out of the way first, collations are like code pages within SQL Server, they allow you to hold different languages of data and sort orders.

If you got this error then open up a query window to your database and do this...

USE <yourdb>
GO

print 'My database [' + db_name() + '] collation is: ' + cast( DATABASEPROPERTYEX ( db_name(), N'Collation' ) as varchar(128) )

print 'My tempdb database collation is: ' + cast( DATABASEPROPERTYEX ( 'tempdb', N'Collation' ) as varchar(128) )

If you get the same collation returned from both then you are probably ok at the database level, for temporary (# or ##) table usage, if they return a differnt collation (and note these are different collations SQL_Latin1_ is not the same as Latin1_ like this for instance...

My database [Training_UKUGCopy] collation is: SQL_Latin1_General_CP1_CI_AS
My tempdb database collation is: Latin1_General_CI_AS

If thats different then you've immediately got a potential problem using temporary tables as shown here:-

create table test_collation (
   title varchar(500) not null
)

insert test_collation ( title )
   values( 'trevor dwyer' )

create table #test (
   title varchar(500) not null
)

insert #test ( title )
   values( 'trevor dwyer' )

select *
from test_collation t
   inner join #test tt on tt.title = t.title

Run this and you'll get this error:-

Msg 468, Level 16, State 9, Line 1

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

Why oh why? Its because of the CREATE TABLE #, your two databases are in different collations so when you create your # table the column 'title' gets the default database collation so when you try and join the two tables together and because of the collation conflict you get an error.

We can resolve this by fixing the collation on the # table...

create table #test (
   title varchar(500) COLLATE SQL_Latin1_General_CP1_CI_AS not null
)

This is not just limited to tempdb, if you have you are joining between different databases for example Customers, Sales and they have different collations then you'll get the same problem. Its also not limited to the server, if you are using linked servers then collation can be a problem too.

We've seen how to 'fix' the temporary table, another way is to 'fix' the join; because we know the collation of our local table we can cast the collation of our remote table, for instance:-

select top 10 *
from torversrv03.torver_ukug.dbo.registrations r
   inner join #test t on t.title = r.fullname COLLATE SQL_Latin1_General_CP1_CI_AI

You need to becareful on which side of the join you put the COLLATE otherwise you will hurt performance because you will turn what might a nice index seek into some form of scan or hash join...

Collations extend right the way from server to value (Server, Database, Column and value as intersected between column / row when using sql_variant, note the collation is not assigned to the sql_variant data type but to the actual value held within it...

declare @one sql_variant
set
@one = 'tony rogerson' COLLATE SQL_Latin1_General_CP1_CI_AI

declare @two sql_variant
set
@two = 'tony rogerson' COLLATE Latin1_General_CI_AI

if @one = @two 
   
print 'match'
else
   print 'no match'
go

declare @one sql_variant
set
@one = 'tony rogerson' COLLATE SQL_Latin1_General_CP1_CI_AI

declare @two sql_variant
set
@two = 'tony rogerson' COLLATE SQL_Latin1_General_CP1_CI_AI

if @one = @two 
   print 'match'
else
   print 'no match'

I think that will do as an introduction to collations and will hopefully cover any problem you have, there is a ton of other considerations that mainly extend out of SQL Server which I'm not inclined to go into in this entry.

Filed under:

Comments

# re: Collations in SQL Server, examples and restrictions

12 July 2006 13:04 by GBN

Remember you can use "COLLATE Database_Default" to avoid hard coding any specific collations anywhere.

# re: Collations in SQL Server, examples and restrictions

12 July 2006 13:18 by tonyrogerson

Good catch, I've never used that; and that will sort the temporary table problem out ->

create table #test (
   title varchar(500) COLLATE database_default not null
)

# re: Collations in SQL Server, examples and restrictions

12 July 2006 14:27 by Colin Leversuch-Roberts

was in my best practices < grin >

# Interesting Finds: July 12, 2006

13 July 2006 04:20 by Jason Haley

# re: Collations in SQL Server, examples and restrictions

14 July 2006 16:36 by beckylou

It's also important to note that if you do a default SQL installation the collation depends on your Regional settings;

English (United States) -> SQL_Latin1_General_CP1_CI_AS
English (United Kingdom) -> Latin1_General_CI_AS

This becomes a real pain if you're building multiple servers and end up with master/tempdb etc being different on each server.

# re: Collations in SQL Server, examples and restrictions

14 July 2006 16:55 by tonyrogerson

Thanks Becky Lou, very good point!

Actually, I'll put in a suggestion that you can point the set up wizard at a server and say 'same collation as ....' and then confusion will go away and no need to check....

:)

# re: Collations in SQL Server, examples and restrictions

17 July 2006 12:37 by Colin Leversuch-Roberts

I believe that the install options are different for a cluster too .. don't quote me as I haven't done a cluster install but I had to send nodes back to be rebuilt as they had the incorrect collation.

# re: Collations in SQL Server, examples and restrictions

25 July 2006 07:50 by HSPoulsen

What do you do when you have multiple servers with different collations, and you really, really wants to fix this?

TIA
Henrik Staun Poulsen

# re: Collations in SQL Server, examples and restrictions

25 July 2006 08:23 by tonyrogerson

I don't know of a way to change the default database collation wants you specified it, you can change a column collation only by creating a new column with the correct collation and copying the data in and using COLLATE to cast to the new collation.

For servers - rebuild master/re-install.

Realistically you are probably going to have to copy data out using bcp and then re-create the database and tables with the correct collation and bulk insert the data back in.

To get round the tempdb problems use the default_database option of COLLATE on the create table, not sure you can do that with SELECT .. .INTO # though - probably, i'd have to try...

# re: Collations in SQL Server, examples and restrictions

25 July 2006 08:44 by HSPoulsen

Oh, no, please don't say that.
bcp is probably not an option, due to disk space.
I guess, we'll have to wait until a thirdpart tool arrives.