A SARGability Riddle In SQL Server: Why Do Some Queries Seek and Some Queries Scan?

Use The Force


I had a RABID FAN ask me an interesting question about a query. I can’t use theirs, but I can repro the question.

The question was: if using ISNULL in a where clause isn’t SARGable, how come I can use a FORCESEEK hint in this query?

Formatting and linking my own, of course.

The query looked something like this:

SELECT
    c = 
        COUNT_BIG(*)
FROM dbo.Users AS u WITH(FORCESEEK)
WHERE ISNULL(u.Id, 0) = 22656;
GO
SQL Server Query Plan
seekable!

What Gives?


The first thing you should notice is that the optimizer throws out ISNULL, here.

Why? Because the Id column isn’t NULL-able, and since ISNULL is Microsoft’s special non-ANSI baby, it can do this one special thing.

If we use COALESCE instead, we’ll get an error.

SELECT
    c = 
        COUNT_BIG(*)
FROM dbo.Users AS u WITH(FORCESEEK)
WHERE COALESCE(u.Id, 0) = 22656;
GO

Msg 8622, Level 16, State 1, Line 8
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

And if we try to use ISNULL on a NULL-able column like Age, we’ll get the same error:

SELECT
    c = 
        COUNT_BIG(*)
FROM dbo.Users AS u WITH(FORCESEEK)
WHERE ISNULL(u.Age, 0) = 22656;
GO

Coacase? Caselesce?


Under the covers, COALESCE is just a crappy band CASE expression.

Without the FORCESEEK hint, we can get the query to actually run.

SELECT
    c = 
        COUNT_BIG(*)
FROM dbo.Users AS u
WHERE COALESCE(u.Id, 0) = 22656;
GO 
SQL Server Query Plan
southa

And ISNULL is just… ISNULL.

SELECT
    c = 
        COUNT_BIG(*)
FROM dbo.Users AS u
WHERE ISNULL(u.Age, 0) = 22656;
GO
SQL Server Query Plan
ribs

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.



5 thoughts on “A SARGability Riddle In SQL Server: Why Do Some Queries Seek and Some Queries Scan?

  1. You weren’t kidding about the crappy band part. I sincerely hope they found gainful employment as Walmart greeters or shoe salespeople/persons/thangs.

  2. Another good reason to dislike nullable columns
    Play dba god and change the defaults so that unspecified column creates are created NOT NULL instead of NULL

    –Alter DB Setting
    ALTER DATABASE Test SET ANSI_NULL_DEFAULT OFF;
    GO
    –and alter connection level setting
    — or change in SSMS
    SET ANSI_DEFAULTS OFF;
    –Create table t1.
    CREATE TABLE t1 (a TINYINT) ;
    GO
    –NULL INSERT should fail.
    INSERT INTO t1 (a) VALUES (NULL);

    — gives
    Msg 515, Level 16, State 2, Line 10
    Cannot insert the value NULL into column ‘a’, table ‘master.dbo.t1’; column does not allow nulls. INSERT fails.
    The statement has been terminated.

    Might be a good change to make in the model database

Comments are closed.