SQL Server 2008 R2 – Using CHECKSUM in WHERE Clause

sql-server-2008-r2t-sql

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.

Best Answer

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
Related Question