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.

# re: Collations in SQL Server, examples and restrictions

25 July 2006 09:31 by beckylou

I believe for normal databases you can use ALTER DATABASE, then ALTER TABLE on all the character fields.

Master/tempdb are the problematic ones as you have to use the commandline tools (Rebuild master Utility) to do this which involves taking SQL Server offline and then using sp_attach_db on all the databases as they 'vanish'.

# re: Collations in SQL Server, examples and restrictions

25 July 2006 09:42 by tonyrogerson

I missed that one - yes, you can now use ALTER DATABASE on non-system databases to change the default database collation.

But you can't do it at the table level because its the actual columns that have the collation and the ALTER TABLE... ALTER COLUMN doesn't support COLLATE so you still need to create a new column with the correct collation, copy the data using COLLATE to cast to the new collation and then drop the old column and rename the new.

Tony.

# re: Collations in SQL Server, examples and restrictions

27 July 2006 08:06 by HSPoulsen

I've been thinking about this problem for a while now.
Here is my manual solution.
Will it work?

a) create a new database with the same size, but with new collation
b) script the old database, and do a search and replace on the collation.
c) for each table in the database run something like this:

DECLARE @debug int
DECLARE @FieldList nvarchar(max)
DECLARE @tablename nvarchar(max)
DECLARE @dbname nvarchar(max)
DECLARE @err int
DECLARE @sql nvarchar(max)
SET NOCOUNT ON
SET @debug = 1
SET @tablename = 'Mytable'
SET @dbname = 'MyDb'
SET @FieldList = ''
SELECT @FieldList = @FieldList + ', ' + b.name
FROM sys.sysobjects a
INNER JOIN sys.syscolumns b on a.id = b.id
INNER JOIN sys.systypes c on b.xtype= c.xtype
WHERE a.xtype = 'u'
AND a.name = @tablename
AND c.name != 'sysname'
SELECT @FieldList = stuff(@FieldList, 1, 1, '')
IF @debug = 1 SELECT @FieldList
BEGIN try
 SELECT @sql = 'SET IDENTITY_INSERT ' + @tablename + ' on
 INSERT INTO dbo.' + @tablename + ' (' + @FieldList + ') SELECT ' + @FieldList + ' FROM ' + @dbname + '.dbo.' + @tablename + '
 SET IDENTITY_INSERT ' + @tablename + ' off
 '
 IF @debug = 1 SELECT @sql
 EXEC @err = sp_executesql @sql
END try
BEGIN catch
 IF (XACT_STATE()) = -1 ROLLBACK TRANSACTION;
 SELECT error_Number(), error_message()
 RAISERROR ('An error occured: ', 10, 1) WITH NOWAIT
END catch

Do you see any problems?

# re: Collations in SQL Server, examples and restrictions

01 August 2006 15:19 by Victoria Holt

The sharepoint Portal Server 2003 creates databases using a specific collation.

The sharepoint database collation is  Latin1_General_CI_AS_KS_WS (MS default to match the file system)

So when your sql server standard collation is Latin1_General_CI_AS  I wonder how tempdb will behave.

# re: Collations in SQL Server, examples and restrictions

03 August 2006 07:56 by GBN

To change a db collation use this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;325335#top

Or:
1. Script the database from EM - *no* "Table Scripting Options" at all
2. Create new db from this script/rename old db etc
3. Use DTS wizard as per KB (no constraints etc)
4. Create another script but *only* "Table Scripting Options"
5. Apply this 2nd script
6. Use red gate compare bundle to check before and after shots

# re: Collations in SQL Server, examples and restrictions

03 August 2006 07:57 by GBN

Sharepoint code is desigend to work with mismatched colaltions, so is Reporting Services

# re: Collations in SQL Server, examples and restrictions

16 October 2007 17:47 by Razvan Socol

Tony,

You wrote: "when using sql_variant, note the collation is not assigned to the sql_variant data type but to the actual value held within it..."

I don't think this is accurate. Try this:

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

select convert(varbinary(50),@one)

select convert(varbinary(50),@two)

if @one = @two

  print 'match'

else

  print 'no match'

if convert(varbinary(50),@one) = convert(varbinary(50),@two)

  print 'match'

else

  print 'no match'

You will see that @one and @two contain the same bits, but they are still not considered equal (I'm not sure if this is a bug or a feature...).

Razvan

# re: Collations in SQL Server, examples and restrictions

16 October 2007 18:26 by tonyrogerson

Hi Razvan,

This is the behaviour I'm talking about - see how SQL_VARIANT_PROPERTY returns the different collations at the row/column level and how the joins work differently because the two rows have different or the same collation.

I'm not exactly sure why converting to varbinary loses the collation, but it's probably because the collation type is stored seperately from the actual data and its the data that is being converted.

Anyway - here is proof of what I was talking about ->

declare @coll table (

   d   sql_variant null

)

insert @coll ( d ) values( 'tony' collate SQL_Latin1_General_CP1_CI_AI )

insert @coll ( d ) values( 'tony' collate Latin1_General_CI_AI )

select *, sql_variant_property( a.d, 'Collation' )

from @coll a

select *, sql_variant_property( a.d, 'Collation' )

from @coll a

   inner join @coll b on a.d = b.d

go

declare @coll table (

   d   sql_variant null

)

insert @coll ( d ) values( 'tony' collate SQL_Latin1_General_CP1_CI_AI )

insert @coll ( d ) values( 'tony' collate SQL_Latin1_General_CP1_CI_AI  )

select *, sql_variant_property( a.d, 'Collation' )

from @coll a

   inner join @coll b on a.d = b.d

go