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.

Later, I checked that all 4 collations are equivalent to the source database characterset and sorting.

Latin1_General_BIN

Latin1_General_BIN2

Latin1_General_100_BIN2

Latin1_General_100_BIN2_UTF8

Could you share your expert advice on the difference between the above collations and which one is best?

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