Solution of SQL Server Collation Conflict

Introduction

Sometimes you may get mentioned below error while executing a SQL stored procedure or performing some select or join T-SQL statement in SQL Server.

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.

The SQL Server throws this exception mostly when the join T-SQL statement is performed and collate of the table is not compatible with each other. Here in this blog, we will discuss how to resolve SQL Server collation conflict issues.

Getting Started

Before the resolution of the SQL Server Collation issue, I would like to ask you to read about the SQL Collate given at the blow of this blog.

Reason for SQL Server Collation Conflict

Mostly conflict occurs when comparing or joining two columns of different tables in the select or join T-SQL statement and their collate of the column is different as shown in the image below. SQL Server supports multiple collations but cannot properly deal with collation mismatches. If a user tries to compare fields with different collations, SQL Server will return the error mentioned in above this blog with collation names.

For Example: Let’s say I have two different tables having the names Table_BookMaster and Table_CodeMaster as shown below image. The column Column_BookName is common in both tablets but if you look closely the collate name of the column in both tables is different. In this case, if you want a join operation as given below in the tables then you will get a SQL collation conflict exception.

 SELECT   
  B.Column_ID,   
  B.Column_BookName,   
  C.Column_BookCode   
 FROM  BookMaster B   
 JOIN CodeMaster C ON B.Column_BookName = C.Column_BookName  

Solution

1. Permanently change the collation name of columns.
2. Add collate in query while comparing the column.

Permanently change the collation name of columns.

To avoid conflict between two columns’ collation of both tables, change the collate name of the common column to the same collate name or change one of the column's collate same as to the other column collate . To change the collate name of columns permanently, the following below code changes the collate name of the common column of both tables. This logic is applicable when you are dealing with permanent SQL tables only.

 
  --sql server collation sql_latin1_general_cp1_ci_as is changed to  Latin1_General_CI_AS
  ALTER TABLE CodeMaster   
  ALTER COLUMN Column_BookName VARCHAR(150) COLLATE Latin1_General_CI_AS NOT NULL  

In above code example, the collate name of column Column_BookName of table CodeMaster is changed to same as collate name of table BookMaster

Add collation in query while comparing the column.

This is effective when you are dealing with a temporary table. Because we can’t change the collate of the temporary table at run time. But you can use it with the permanent tables also if your intention is not to change the collate name permanently. The below examples describe how to add collate with SQL query.

SQL Collation with Join

 SELECT   
      B.Column_ID,  
      B.Column_BookName,  
      C.Column_BookCode   
 FROM BookMaster B   
 JOIN CodeMaster C ON B.Column_BookName=C.Column_BookName COLLATE SQL_Latin1_General_CP1_CI_AS  

SQL Collation with the subquery

 SELECT   
      B.Column_ID,  
      B.Column_BookName,  
 FROM BookMaster B   
 WHERE B.Column_BookName IN(SELECT Column_BookName COLLATE SQL_Latin1_General_CP1_CI_AS FROM CodeMaster)  

SQL Collation with Where clause

 SELECT Column_BookName FROM BookMaster WHERE Column_BookName COLLATE SQL_Latin1_General_CP1_CI_AS ='Kailashs Blogs'  

What is SQL Collate

Collate is a set of rules that tells the database engine how to compare and sort the character data in SQL Server. In SQL Server the collate can be set in different levels as given below.

1. SQL Server level
2. Database level
3. Column level

SQL Server Level Collation

This is the default collation for all the system databases and user databases, and this can be set up during the installation of the SQL server as shown in the image.

Database Level Collation

Database-level collations are inherited from SQL Server level collations if no collate is specified while creating a database. The below image shows the current collate of the database.

Column Level Collation

By default, a new character type column inherits the collation of the database unless you specify the collation explicitly while creating the table. Collation setting can be specified on the below character column types:

1. VARCHAR
2. VVARCHAR
3. CHAR
4. NCHAR
5. TEXT
6. NTEXT

Summary

Here in this blog post, we learn how to solve the collations confit and different levels of SQL Collate in SQL Server. I hope this blog post is helpful to you.

Thanks