Skip to content | Site Map
Contact Details
P 07 3278 4325
M 0420 837 445
E support@ andrewrowlings.com
Post PO Box 2004
Graceville East
QLD 4075
ABN 202 3456 3434

Focused on software development with a professional approach and a commitment to quality. 



Other Articles

SQL Server Collation

5th June 2006

Introduction

SQL Server allows collation settings to be made. This governs things like sort orders, character values and code pages. It is not the most interesting technical topic that can be dissected, but if you do have an interest, then I recommend reviewing Michael Kaplan as a starting point.

Collation - Be Careful!

In SQL Server 7.0 the collation setting was the same across all databases within an instance of the server. This was very straight forward and you probably didn't have much to do with collation.

But with SQL Server 2000 came the ability to set different collation settings down to the column level. This means that within the same database, columns in different tables, or indeed, columns within the one table, may have different collation settings.

And so you may have come across this error:

Cannot resolve collation conflict for equal to operation.

The SQL Server 2005 version of this message gives a little more information ...

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

This means that different collation settings within the scope of the query have resulted in an unresolvable conflict. For example, you can't compare the length of a word in English to a word in Welsh (but we all know the Welsh name will be longer!)

So how can this happen? Usually you won't have been going around changing the collation setting of different columns, but, what can easily happen is the following...it is something to watch out for!

  1. An application goes live that uses SQL Server. The default collation setting is SQL_Latin1_General_CP1_CI_AI, but this gets changed to something else, eg. Latin1_General_CI_AS. All database objects created will inherit this modified collation setting. (Exactly why it got changed will depend on the geek level of your DBA!)
  2. A few years down the track and some modifications are being made. A dev/test environment is setup, and maybe even a staging/UAT environment. SQL Server is installed in these environments with all its default settings, including collation SQL_Latin1_General_CP1_CI_AI.
  3. A release is prepared. SQL Scripts are generated. They look like this:
CREATE TABLE [dbo].[Items](
	[Label] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AI,
	[col2] [int] NULL,
	[col3] [int] NULL
) ON [PRIMARY]

Oh oh...that pesky collation setting has been included and when the release script gets run on production you now have more than one collation setting in use. So imagine that the original database contains this table...

CREATE TABLE [dbo].[Lists](
	[ListId] [int],
	[Label] [varchar](5) COLLATE Latin1_General_CI_AS NULL,
	[col2] [int] NULL,
	[col3] [int] NULL,
	[col4] [int] NULL
) ON [PRIMARY]

and your newly released code runs a query that joins the original table "Lists" with the new table "Items"...

select * from Items I inner join Lists L on I.Label = L.Label

bingo! - you get the error.

If you are really unlucky you will find yourself in a situation like I was where it is an ASP page calling a stored procedure that contains the join and all you see is a blank web-page that eventually times out!

Conclusion

In conclusion, my advice is to always have collation in the back of your mind, even if you are not developing a multi-lingual, multi-regional application, as it could just be the underlying cause of an issue that has been frustrating you for weeks.



Print this page Print this page