SQL SERVER – Cannot Resolve Collation Conflict For Equal to Operation

Cannot resolve collation conflict for equal to operation.

In MS SQL SERVER, the collation can be set at the column level. When compared to 2 different collation columns in the query, this error comes up.

SELECT ID
FROM ItemsTable
INNER JOIN AccountsTable
WHERE ItemsTable.Collation1Col = AccountsTable.Collation2Col

If columns ItemsTable.Collation1Col and AccountsTable.Collation2Col have different collation, it will generate the error “Cannot resolve collation conflict for equal to operation“.

To resolve the collation conflict add the following keywords around “=” operator.

SELECT ID
FROM ItemsTable
INNER JOIN AccountsTable
WHERE ItemsTable.Collation1Col COLLATE DATABASE_DEFAULT
= AccountsTable.Collation2Col COLLATE DATABASE_DEFAULT

Watch this quick video for resolution:

Collation can affect the following areas:

  1. Where clauses
  2. Join predicates
  3. Functions
  4. Databases (e.g. TempDB may be in a different collation database_default than the other databases some times)

Checkout following video how collation can change the order of the column:

Just like collation, there is another problem with SQL Server and that is related to Parameter Sniffing, I have written a detailed blog post about how the parameter sniffing can be resolved in SQL Server.

Here are some really good blog posts which you can read about parameter sniffing and how you can avoid the problem related to it.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Menu
Exit mobile version