Forum Discussion

sljesra27's avatar
sljesra27
Copper Contributor
Dec 11, 2024
Solved

SQL Server Collation

Hi Experts, I am doing cross database migration. source database characterset is ISO_1 (ISO 8859-1) and binary sorting. While checking equivalent collation in MS SQL, I found Latin1_General_BIN. L...
characters
database
  • petevern's avatar
    petevern
    Jan 25, 2025

    Hereby a short overview of the main differences between these collations.
    What is best depends on the needs and compatibility of the application(s) using this SQL.

    Latin1_General_BIN:

    Character Set: ISO 8859-1 (ISO_1)
    Sorting Method: Binary
    Introduced In: Pre-SQL Server 2008
    Unicode Support: Limited
    UTF-8 Support: No
    Best Use Case: Suitable for legacy compatibility with older systems.

    Latin1_General_BIN2:

    Character Set: ISO 8859-1 (ISO_1)
    Sorting Method: Binary 2 (modern)
    Introduced In: SQL Server 2008
    Unicode Support: Yes
    UTF-8 Support: No
    Best Use Case: Recommended for non-Unicode or backward-compatible migrations.

    Latin1_General_100_BIN2:

    Character Set: Unicode (UCS-2)
    Sorting Method: Binary 2 (modern)
    Introduced In: SQL Server 2008 R2
    Unicode Support: Full
    UTF-8 Support: No
    Best Use Case: Ideal for modern Unicode handling and compatibility.

    Latin1_General_100_BIN2_UTF8:

    Character Set: Unicode (UTF-8)
    Sorting Method: Binary 2 (modern)
    Introduced In: SQL Server 2019
    Unicode Support: Full
    UTF-8 Support: Yes
    Best Use Case: Best choice for UTF-8 encoding, multilingual data, and storage efficiency.

petevern's avatar
petevern
Brass Contributor
Jan 25, 2025

Hereby a short overview of the main differences between these collations.
What is best depends on the needs and compatibility of the application(s) using this SQL.

Latin1_General_BIN:

Character Set: ISO 8859-1 (ISO_1)
Sorting Method: Binary
Introduced In: Pre-SQL Server 2008
Unicode Support: Limited
UTF-8 Support: No
Best Use Case: Suitable for legacy compatibility with older systems.

Latin1_General_BIN2:

Character Set: ISO 8859-1 (ISO_1)
Sorting Method: Binary 2 (modern)
Introduced In: SQL Server 2008
Unicode Support: Yes
UTF-8 Support: No
Best Use Case: Recommended for non-Unicode or backward-compatible migrations.

Latin1_General_100_BIN2:

Character Set: Unicode (UCS-2)
Sorting Method: Binary 2 (modern)
Introduced In: SQL Server 2008 R2
Unicode Support: Full
UTF-8 Support: No
Best Use Case: Ideal for modern Unicode handling and compatibility.

Latin1_General_100_BIN2_UTF8:

Character Set: Unicode (UTF-8)
Sorting Method: Binary 2 (modern)
Introduced In: SQL Server 2019
Unicode Support: Full
UTF-8 Support: Yes
Best Use Case: Best choice for UTF-8 encoding, multilingual data, and storage efficiency.

Resources