BTerrell Group Blog

I was working on a project last week that required me to create a JOIN that links two tables in the same database. Typically this is a relatively simple process, but when I tried to execute my query, I got the following error message:

I was surprised to see this message since a single collation is usually used throughout a database. I checked the collation for each column in the tables by using the following query:

                SELECT col.name, col.collation_name

                FROM sys.columns col

                WHERE object_id = OBJECT_ID(‘TableName’)

I found that the two tables I wanted to join were indeed using two different collation methods. In order to get around this issue, I forced which collation I wanted to use by using the COLLATE clause:

                SELECT *

                FROM Table1

                JOIN Table2

                ON Table1.ColumnA = Table2.ColumnA COLLATE SQL_Latin1_General_CP1_CI_AS

You can change either the first table’s collation method to match the second, or vice versa. You can also use COLLATE DATABASE_DEFAULT to use the default collation method set in the database properties.

I hope this will save you some time in the future when dealing with collation issues.