none
SQL Server 2005 (90) versus 2008 (100) Binary Collation BIN2

    Question

  • I cannot find any information detailing the internals of collation and the actual difference between versions of collation.
    I would like to know what the actual internals are and the difference between Latin1_General_BIN2 and Latin1_General_100_BIN2?
    Can anyone help?  Also if you can where did you get the information?

    Thanks,
    Justin...
    Monday, March 30, 2009 3:44 PM

Answers


  • Hi Jiggle,

    Latin1_General_BIN2 is used in SQL Server 2005 and Latin1_General_100_BIN2 is for SQL Server 2008. However, there are more new Windows collations available in Latin1_General_100_ than in Latin1_General_. To compare Latin1_General_100_ with Latin1_General_, please see the All Windows Collations table in the following link: http://msdn.microsoft.com/en-us/library/ms188046.aspx.


    If you have any more questions, please let me know.

    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Wednesday, April 1, 2009 8:34 AM
    Moderator

All replies


  • Hi Jiggle,

    Latin1_General_BIN2 is used in SQL Server 2005 and Latin1_General_100_BIN2 is for SQL Server 2008. However, there are more new Windows collations available in Latin1_General_100_ than in Latin1_General_. To compare Latin1_General_100_ with Latin1_General_, please see the All Windows Collations table in the following link: http://msdn.microsoft.com/en-us/library/ms188046.aspx.


    If you have any more questions, please let me know.

    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Wednesday, April 1, 2009 8:34 AM
    Moderator
  • The accepted answer doesn't actually provide an answer.

    First, to clarify the title of the question: SQL Server 2005 is version 90, but collations without any version number are actually version 80. The version number refers to the version of SQL Server in which those collation rules were introduced, but that doesn't necessarily mean that it was the version that first provided a specific collation (or group of collations) using those rules. For example, SQL Server 2012 introduced new collations with explicit support for supplementary characters (i.e. collations ending in "_SC"). However, there are no version 110 collations (SQL Server 2012 = version 110 {or 11.0, or 11 because you can never have too many ways of representing the same thing ;-) }). Instead, they added new collations that were basically duplicates of version 90 and version 100 collations but with "_SC" appended to the names. But, neither SQL Server 2005 (version 90), nor SQL Server 2008 (version 100), nor even SQL Server 2008 R2 (version 105) have any collations ending in "_SC".

    All of that to say: Latin1_General_BIN2 was introduced in SQL Server 2005 (version 90), but it's still a version 80 collation (rules introduced in SQL Server 2000).

    ----------

    While it is fairly minor, there is a functional difference between Latin1_General_BIN2 and Latin1_General_100_BIN2: the version 100 collation has 438 uppercase + lowercase mappings (used for UPPER() and LOWER() functions, respectively) that are not present in the version 80 (or even version 90) collations.

    Other than that, they should be identical in behavior.

    So, if you have a choice, always go with the most recent version of a collation, in this case: Latin1_General_100_BIN2.

    For more details, including test code, please see:

    Differences Between the Various Binary Collations (Cultures, Versions, and BIN vs BIN2)

    Take care, Solomon...

    P.S. To whoever marked this answer as "abusive": Huh? This answer actually answers the question. It is not off topic (quite the opposite in fact), or spam/advertising, does not contain any profanity or inappropriate/adult content, and does not violate the code of conduct. If there is a question or concern, please reply to this answer so that I can address the issue. Thanks.

    Tuesday, May 14, 2019 3:04 PM