Andy Warren (4/7/2016)
Hugo, is there really a difference? In both cases its clearly something unusual being done and I'd argue in both cases a "why" comment would be appropriate.
Yes, there is really a difference.
Hugo Kornelis (4/7/2016)
Stewart "Arturius" Campbell (4/6/2016)
Interesting, never thought of doing it that way
Nice question, thanks Horia
Nor should you. In production code, I would always write out the explicit intent instead of relying on such a trick.
ORDER BY CASE WHEN val IS NULL THEN 2 ELSE 1 END, -- Ensure that NULL sorts last
val -- Within non-NULL, order by ascending value
I'm not sure I'd call it a trick -- it's using a documented aspect of the SQL-92 standard (see my post on an early question at http://www.sqlservercentral.com/Forums/FindPost1768805.aspx
Having said that, it's not a commonly-known aspect, so at least commenting how it works would seem in order.
I'm not sure whether a CASE with a NULL test on every row would be more or less performant than a *-1 on every row, but that's something to consider as well.
No, Hugo is right, it has to be considered a trick rather than a valid technique.
If the column had been declared with a numeric type other than int or bigint or smallint it would have been a reliable technique, not a trick, but as the declaration given uses int, here we have an unreliable trick.
It will usually work, but what happens if one of the integer values is -2147483648 ? Applying minus to that will cause arithmetic overflow, so it's unsafe to use with ints. It's equally unsafe with bigints (try it with the value -9223372036854775808). It works for smallints because (contrary to BOL, so presumably this is a bug in SQL Server) unary minus on a smallint promotes the result to int. It works for tinyint (unary minus is documented as promoting tinyint to smallint; unary minus obviously can't deliver tinyint because because there are no negative tinyints). It works for all the non-integer numerics (because their negative values are handled with a separate sign bit, not using twos complement so that the sign bit can be part of the absolute magnitude).