Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

BIN2 collations do not come through SqlCompareOptions of SqlString

The System.Data.SqlTypes.SqlString struct ( http://referencesource.microsoft.com/#System.Data/System/Data/SQLTypes/SQLString.cs ) has a private variable m_flag that is of type SqlCompareOptions (a flags enum). When a SQLCLR method is called, SQL Server passes in a parameter of type SqlCompareOptions. This enum has the following values:

public enum SqlCompareOptions {
None = 0x00000000,
IgnoreCase = 0x00000001,
IgnoreNonSpace = 0x00000002,
IgnoreKanaType = 0x00000008, // ignore kanatype
IgnoreWidth = 0x00000010, // ignore width
BinarySort = 0x00008000, // binary sorting
BinarySort2 = 0x00004000, // binary sorting 2
}

All of those options come through EXCEPT BinarySort2 (i.e. 0x4000) which should be set when using a collation ending in _BIN2. Instead, when using a BIN2 collation, SqlCompareOptions is set to 0 / None. I would expect that using a BIN2 collation would:

1) set the value of SqlCompareOptions to 0x4000 (i.e. decimal 16384),

AND

2) equate to the "Ordinal" value of the CompareOptions enum ( https://msdn.microsoft.com/en-us/library/system.globalization.compareoptions.aspx ) in the CompareOptionsFromSqlCompareOptions method (exposed publicly and used internally). Currently, if this is fixed such that BIN2 does come through as 0x4000 / 16384, the CompareOptionsFromSqlCompareOptions method will throw an exception. For some reason, this method does a test on line 753 ( http://referencesource.microsoft.com/#System.Data/System/Data/SQLTypes/SQLString.cs,753 ) for both BIN and BIN2 collations. If the BIN collations have no .NET equivalence ( https://msdn.microsoft.com/en-us/library/ms143350.aspx ), then it makes sense enough to throw an exception when using them. But the documentation states that the newer BIN2 collations do have an equivalence in .NET ( https://msdn.microsoft.com/en-us/library/ms143515.aspx ), hence stating that "The advantage of using a Binary-code point sort order is that no data resorting is required in applications that compare sorted SQL Server data".

Regarding the test code in "Steps to Reproduce": I used the .NET type of "object" (equating to SQL_VARIANT) instead of "SqlString" for simplicity of testing. Using SQL_VARIANT makes it is possible to set the collation on the fly when using a UDF. However, the same behavior can be seen when using SqlString and changing the default collation of the database where the CLR UDF exists.

0 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    AdminMicrosoft SQL Server (Admin, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    closed  ·  AdminMicrosoft SQL Server (Admin, Microsoft Azure) responded  · 

    Thank you for your patience. I have created a tracking item in our content VSTS issue tracking system. For the most part, content feedback can now be made directly to our topics as our new documentation platform supports comments directly on the topic.
    https://docs.microsoft.com/en-us/sql/sql-server/sql-server-technical-documentation

    As well as you can edit the topic yourself by clicking the “Edit” button on the topic. For help in editing, see https://docs.microsoft.com/en-us/sql/sql-server/sql-server-docs-contribute.

    1 comment

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base