Things SQL Server vNext Should Address: ISNULL SARGability

Preferential

---

Of course, I’d love if everyone lived in the same ivory tower as me and always wrote perfect queries with clear predicates that the optimizer understands and lovingly embraces.

But in the real world, out in the nitty gritty, queries are awful. It doesn’t matter if it’s un-or-under-trained developers writing SQL, or that exact same person designing queries in an ORM. They turn out horrible and full of nonsense, like drunk stomachs at IHOP.

One of the most common problems I see is people getting lazy with checking for NULLs, or overly-protective about it. In “””””real””””” programming languages, NULLs get you errors. In databases, they’re just sort of whatever.

Thing Is

---

When you create a row store index on a column, whether it’s ascending or descending, clustered or nonclustered, the data is put in order. In SQL Server, that means NULLs are sorted together. Despite that, ISNULL still creates problems.

DROP TABLE IF EXISTS #t;

SELECT
    x.n
INTO #t
FROM
(
SELECT
    CONVERT(int, NULL) AS n

UNION ALL

SELECT TOP (10)
    ROW_NUMBER() OVER
    (
        ORDER BY
            1/0
    ) AS n
FROM sys.messages AS m
) AS x;


CREATE UNIQUE CLUSTERED INDEX c 
ON #t (n) WITH (SORT_IN_TEMPDB = ON);

In this table we have 11 rows. One of them is NULL, and the other 10 are the numbers 1-10.

Odor By

---

If we select an ordered result, we get a simple query plan that scans the clustered index and returns 11 rows with no Sort operator.

However, if we want to replace that NULL with a 0, things get goofy.

Wheredo

---

Something similar occurs when ISNULL is applied to the where clause.

There’s one NULL. We know where it is. But we still have to scan 10 other rows. Just in case.

Conversion

---

The optimizer should be smart enough to figure out simple use of ISNULL, like in both of these cases.

I’m sure wiser people can figure out deeper cases, too, and even apply them to more functions that involve some types of date math, etc.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.