1

I'm going through a long stored procedure and I found this, as part of a long query:

SELECT ....
FROM   ---
WHERE  condition
  AND  CHECKSUM(r.roomprovider_id) <> CHECKSUM(ISNULL(br.roomprovider_id, r.roomprovider_id))

roomprovider_id is an INT value

First time I see it and I've no clue on what is being achieved with this. I made some searches, read the definition of CHECKSUM on the tech pages and couple of sources more but can't understand the point of using CHECKSUM here.

I'm not looking for an answer that explains exactly this case (if possible, even better) but I would be satisfied with an answer explaining the point of this way to use checksum.

  • I bet you'll get a better answer from the person who wrote it, than speculation any of us might have... – Aaron Bertrand Apr 4 '14 at 13:08
  • Person long ago not on the team, code is from 2010, I already asked around, no one has a clue. – Yaroslav Apr 4 '14 at 13:10
  • 4
    Well, if it was obfuscation for the purposes of job security, doesn't seem like it worked out... other than that, and eliminating the possibility of seeking on an index on any of those columns, and reducing confidence in accuracy (since collisions are possible if you expand this technique to more than one value), this seems like an unsafe way to compare two values. – Aaron Bertrand Apr 4 '14 at 13:14
  • Thanks @AaronBertrand, this was more or less my guessing. If you added as answer will check it. – Yaroslav Apr 4 '14 at 13:18
2

Nobody can really tell you exactly why this person coded the WHERE clause this way. I can tell you that I can't think of a reason that form is better than:

r.roomprovider_id <> br.roomprovider_id

(And while I can interpret how the code is currently written, I am not sure of the expected/intended behavior - should a row be included or excluded when the right side is NULL?)

My guesses for their motivation include:

  • job security through obfuscation
  • job security through forcing bad performance, that they could fix later easily, since this will remove the possibility to seek on any of those columns if they are suitably indexed to satisfy the query
  • CHECKSUM() was a shiny new function they just learned, and they just had to implement it somewhere
  • they believed that CHECKSUM() was less prone to inaccuracy from collisions than other methods, or that - with only one input - it served any purpose whatsoever
  • Was reading the SO answer about collisions, and you also mentioned it, so now my bet on why it was used goes for one of the 2 last points. – Yaroslav Apr 4 '14 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.