We ran into a SQL Server Collation issue on a project recently and thought it might be useful to someone to share a bit of information. We don’t encounter this too often but it’s good to understand it or at least know how to Google for it.
A collation is simply a configuration setting that determines how the SQL Server database engine should treat character data. It can be changed/managed at the server, database, or column level. It typically comes into play in handling the language and regional differences that come with supporting users and applications in different parts of the world. Our scenario involved U.S. English and French Canadian collation differences.
To find out what collations are available in SQL Server use:
SELECT name, description FROM sys.fn_helpcollations();
You can use SQL Management Studio or T-SQL statements to set collations at the server, database or column levels. Be careful simply changing collations though. Often these are in place for a specific reason (languages, regions, etc.) so work with stakeholders to understand the best course of action (read: least impact resulting from a collation change). Often it can be safer to just get around collation issues in the queries themselves versus changing server, database or column collations.
For example, in a given Select statement (e.g.):
SELECT table1.CustomerID, table1.CustomerName, table2.PrimaryAddressID FROM Customer table1 JOIN Address table2 ON table1.CustomerID = table2.CustomerID ORDER BY table1.CustomerName;
Because the CustomerID columns are configured with different collations, the database engine returns the following error message: Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_100_CI_AS” in the equal to operation.
To work around the collation issue, we can add the COLLATE clause to the join condition like the below:
SELECT table1.CustomerID, table1.CustomerName, table2.PrimaryAddressID FROM Customer table1 JOIN Address table2 ON table1.CustomerID = table2.CustomerID COLLATE Latin1_General_100_CI_AS ORDER BY e1.EmpID;