Collate Resolution For Free

Collations in SQL Server provide sorting rules, case, and accent sensitivity properties for your data. Collations that are used with character data types, such as char and varchar, dictate the code page and corresponding characters that can be represented for that data type.

Collation conflicts Cannot resolve the collation conflict between ?Latin1_General_CI_AI? and ?SQL_Latin1_General_CP1_CI_AS? in the like operation. This may prompt an issue when programming for SQL Server. Users can resolve this issue by utilizing a SQL Server column compare clause with COLLATE as described above.

To work around this issue, do one of following: Downgrade from SQL Server 2012 SP3 to Cumulative Update 4 (build 11.00. 5569) for SQL Server 2012 Service Pack 2. Change the collations of the involved databases to be the same as the server collation on Azure SQL Database (SQL_Latin1_General_CP1_CI_AS).

In a database, a collation is a set of rules that determine how data is sorted and compared, usually applied to how text data is sorted in different languages for the purpose of indexing text data and for making comparisons between text values. Different languages have different character sets and ordering.

This error occurs when you mix two columns with different collation in the same expression.

The collate clause is used for case sensitive and case insensitive searches in the columns of the SQL server. There are two types of collate clause present: SQL_Latin1_General_CP1_CS_AS for case sensitive. SQL_Latin1_General_CP1_CI_AS for case insensitive.

You need to cast the join columns to the same collation. Ideally you should sort out the collation in the database as casting negates the use of indexes.

The SQL_Latin1_General_CP1_CI_AS collation is a SQL collation and the rules around sorting data for unicode and non-unicode data are different. The Latin1_General_CI_AS collation is a Windows collation and the rules around sorting unicode and non-unicode data are the same.

You can find SQL collation at the server, database, and column level. Another important thing to know is that the collation setting need not be the same at the server, database, and column level. Also, you can update your queries to use specific collations.

Default server-level collation is SQL_Latin1_General_CP1_CI_AS. If you are migrating databases from SQL Server to Azure SQL Managed Instance, check the server collation in the source SQL Server using SERVERPROPERTY(N'Collation') function and create a managed instance that matches the collation of your SQL Server.