Those Dreaded Collations!
By Kalman Toth, M.Phil., M.Phil., MCDBA

May 12, 2005

You do a simple join on email from your local server to a linked server. You get the dreaded message: operation cannot be performed due to collation conflict! Just what are collations?

Collations were introduced with SQL Server 2000. Prior to that there were code pages. Binary code page was the most popular due to its good sort performance.

This is the default collation for SQL Server 2005: Latin1_General_CI_AS . Latin1 is the Latin alphabet CI is Case Insensitive and AS is Accent Sensitive.

Practically you can place the clause anywhere in a statement following a char or varchar column, the following way:

COLLATE Latin1_General_CI_AS

The join will work again. On large tables, there is a performance hit. There is another alternative: change the collation of the table. That option is not always available.

This is an example how to use collation in a SELECT:

USE AdventureWorks
GO
SELECT FirstName FROM HumanResources.Employee
ORDER BY FirstName
COLLATE Traditional_Spanish_ci_ai ASC
GO

To get all the collations, execute the following SELECT:

select * from fn_helpcollations()