Jerry Nixon @Work: SQL 2k Performance: IsNull vs Coalesce

Jerry Nixon on Windows

Tuesday, May 23, 2006

SQL 2k Performance: IsNull vs Coalesce

We need to start with a summary of judgment. IsNull() is unquestionably faster and easier to spell than Coalesce(). Even so, the delta is so minor that it barely matters.

Pass#1, Pass#2 : Statement Executed 1,000,000 times (SQL2k)
------ ------ --------------------------------------------

9280ms, 9280ms : Set @x = IsNull(Jerry, Nixon)
9266ms, 9296ms : Set @x = IsNull(Jerry, Null)
9466ms, 9440ms : Set @x = IsNull(Null, Nixon)
9283ms, 9280ms : Set @x = IsNull(Null, Null)

9500ms, 9500ms : Set @x = Coalesce(Jerry, Nixon)
9516ms, 9500ms : Set @x = Coalesce(Jerry, Null)
9516ms, 9530ms : Set @x = Coalesce(Null, Nixon)
9360ms, 9376ms : Set @x = Coalesce(Null, Null)

The previous results indicate a simple head-to-head of IsNull() and Coalesce(). Each statement was executed 1,000,000 times, twice. The delta between the two passes is very trivial; it is likely based on latent SQL activity during the execution and varies from statement to statement on which pass was fastest. The delta between the corresponding IsNull() and Coalesce() statements is also quite trivial considering each delta has to be divided by 1,000,000 to represent the single-statement gain, but still IsNull() outperforms Coalesce() every time.

Pass#1, Pass#2 : Statement Executed 1,000,000 times (SQL2k)
------ ------ --------------------------------------------

9296ms, 9296ms : Set @x = IsNull(Jerry, IsNull(Nixon, Value))
9300ms, 9296ms : Set @x = IsNull(Jerry, IsNull(Null, Value))
9546ms, 9533ms : Set @x = IsNull(Null, IsNull(Nixon, Value))
9623ms, 9610ms : Set @x = IsNull(Null, IsNull(Null, Value))
9296ms, 9296ms : Set @x = IsNull(Jerry, IsNull(Nixon, Null))
9296ms, 9313ms : Set @x = IsNull(Jerry, IsNull(Null, Null))
9546ms, 9546ms : Set @x = IsNull(Null, IsNull(Nixon, Null))
9453ms, 9453ms : Set @x = IsNull(Null, IsNull(Null, Null))

9580ms, 9563ms : Set @x = Coalesce(Jerry, Nixon, Value)
9576ms, 9563ms : Set @x = Coalesce(Jerry, Null, Value)
9906ms, 9983ms : Set @x = Coalesce(Null, Nixon, Value)
9843ms, 9876ms : Set @x = Coalesce(Null, Null, Value)
9580ms, 9593ms : Set @x = Coalesce(Jerry, Nixon, Null)
9576ms, 9580ms : Set @x = Coalesce(Jerry, Null, Null)
9970ms, 9890ms : Set @x = Coalesce(Null, Nixon, Null)
9686ms, 9686ms : Set @x = Coalesce(Null, Null, Null)

The previous results indicate a special head-to-head of IsNull() and Coalesce(). I have actually nested IsNull() statements to perform the same operation as a three-argument Coalesce() operation. Each statement was executed 1,000,000 times, twice. Like before the pass deltas are inconsequential. The delta between the corresponding IsNull() and Coalesce() statements remains small, but still IsNull() continues to outperform Coalesce().

I was actually surprised at latter results. I would have expected nested IsNull() to under perform Coalesce() as Coalesce() is at home with 2+ arguments.

IsNull() is the undisputed SQL Server 2000 champion.