TSQL query optimizer view on non-nullable ISNULL()

Question

As part of some dynamic SQL (ick), I've implemented the 'sort NULLs last' solution described here: Sorting null-data last in database query

ORDER BY CASE column WHEN NULL THEN 1 ELSE 0 END, column

My question is: On non-nullable columns that have ISNULL() applied to them, will the query optimizer strip this out when it realises that it will never apply?

Answer 1

It's not clear why your question mentions the ISNULL function when that isn't in your code.

ORDER BY CASE column WHEN NULL THEN 1 ELSE 0 END, column

First of all this code doesn't work, it is equivalent to CASE WHEN column = NULL which is not what you need.

It would need to be

ORDER BY CASE WHEN column IS NULL THEN 1 ELSE 0 END, column

The optimisation question is easy to test.

CREATE TABLE #T
  (
     X INT NOT NULL PRIMARY KEY
  )

SELECT *
FROM   #T
ORDER BY X

SELECT *
FROM   #T
ORDER  BY CASE WHEN X IS NULL THEN 1 ELSE 0 END, X 

DROP TABLE #T

The plan shows a sort operation in the second plan indicating that this was not optimised out as you hoped and the pattern is less efficient than ORDER BY X.