Which BINARY COLLATION?

• Kristen-173977

  SSCrazy Eights

  Points: 8687

  More actions

  December 3, 2018 at 1:12 am

  #151613

  Googling around I just read that I should be using a BIN2 collations as BIN has been deprecated since SQL 2005 ... that little gem has passed me by.

  I had always thought that all Binary Collations were equal (but have never got my head around why there are so many ... except perhaps "flexibility" of naming?)

  Does it matter which one I use, although presumably BIN2 is (nowadays) important?

  Going to have to change all the places in the code where the deprecated BIN occurs and triage all that, otherwise-unchanged, code through QA 🙁

  For example: in a Splitter for e.g. CSV the comparison for COMMA is collated "Binary" because I figure it is faster than a case and accent Insensitive comparison

  This issue came about because of something else I was doing that has me scratching my head a bit.

  I have two columns, an [ObjectName] and an [AliasName].  Normally only the ObjectName (FKey to another table where it is defined) column is used, but on rare occasions there is a need to Alias that name usage (same name used twice type thing), hence the extra [AliasName] column

  I was just pondering if I should allow a TAG in the Alias name,e.g. "{DATA}", to be substituted with the ObjectName, because usually the Alias name is just some appended "tie break", and in the event that ObjectName changes there is a risk that changing AliasName would be overlooked ... Heck! it would be a handy shortcut too.

  So I was going to put, in a VIEW,
  
CREATE VIEW MyView
AS
SELECT ObjectName, AliasName, OtherCol1, OtherCol2, ...
      [V_AliasName] = REPLACE(AliasName COLLATE Something_BIN
                              , '{DATA}'
                             , ObjectName COLLATE Something_BIN)
FROM MyTable

  I can't figure out if I even need that final COLLATE ... or maybe I need one after the second parameter too ... it does my head in not being sure about these things, at the end of the day I want best-performance, and superfluous COLLATEs might prevent best-optimisation

  That said, I'm not at all sure that putting a REPLACE in this VIEW is a good idea ... for the 0.1% of cases where it will get used.

  So I'll probably wind up caching/warehousing the stuff to make sure of that ... and then building automated just-in-time refresh of the cache when anythign changes ... that's a lot of code for the convenience of having a "{DATA}" marker to make sure that the user doesn't forget to change AliasName when ObjectName changes ...