Cannot Resolve Collation Conflict

Collation conflicts demystified

A collation conflict occurs when SQL Server attempts to compare or join strings with opposing collation settings. Collations guide SQL Server on character interpretation, affecting sort order and comparison sensitivity (case & accent).

Updating collations

You edited your database collation but found existing objects unaltered. Here's how you can manually update these:

1. Deploy T-SQL scripts that traverse all objects applying the new collation - a Terminator-esque approach but for collations.
2. Rebuild indexes and check constraints post-collation changes. Safety first, they always say!

COLLATE command up-close

Since you don't control space and time yet, you can't alter original data sources' collation. Use COLLATÈ to change collation on-the-fly:

SELECT 
    a.Name COLLATE SQL_Latin1_General_CP1_CI_AS, // We'll have black coffee from Ethiopia
    b.Description 
FROM 
    Table1 a 
JOIN 
    Table2 b 
    ON a.Name COLLATE SQL_Latin1_General_CP1_CI_AS = b.Name COLLATE SQL_Latin1_General_CP1_CI_AS; // and Arabian Sweets, in Heavy Rain


Irrespective of their source collations, this query asserts the same collation for both sides of the join, resolving the conflict smoothly.

Navigating through collation conflicts

Collation update strategy

Before you embark upon changing the database collation, consider the following:

1. Backup your toys before you play rough. Safety first.
2. Script out collation changes to efficiently navigate through all affected objects.
3. Apply changes during low-traffic periods — let's not be the cause of a traffic jam.
4. Lazy testing is for lazy people. Test rigorously after updating to ensure no residue from the conflict remains.

Using scripts for batch updating

Automate updating table and column collations with scripts. They are like little robots that do your bidding:

• Table collations : Create a list of tables to loop through, changing collations via ALTER TABLE.
• Column collations : Identify columns that need updating and execute “ALTER TABLE … ALTER COLUMN” with new collation

Remember to refresh views and stored procedures for the changes to take effect.

Reverting collation changes

If the updates have led to unintended side-effects, going back to the original collation could be the knight in shining armor:

ALTER DATABASE YourDatabase 
COLLATE Latin1_General_CI_AS; // Back to the future!


Remember, this won't change columns and tables explicitly set to a different collation.