Mladen aka spirit1 posted a speed test of COALESCE vs. ISNULL. Reported result: COALESCE is faster.
But leave it to Anatoly Lubarsky to argue with what was posted. He posted his own speed test, showing that ISNULL is faster.
Anatoly's results showed a miniscule difference, "52 seconds" vs.
"52-53 seconds". Mlanden's tests show a larger difference, around 15%.
But I don't trust either of these results.
One thing in common with both of the tests I linked to, and which makes
them both flawed, is that they return data to the client. This factors
greatly into testing time. What if there was a network hiccup, or what
if the client UI did something different when rendering the results?
We're not testing the network's ability to send data or the client's
ability to render it. What's being tested is very specific: Speed of
COALESCE vs. ISNULL.
So this leads me to present Adam's Number 1 Rule of Performance Testing: When performance testing a specific feature, do everything in your power to test only
that feature itself. Isolate your test as much as possible so that
there is no way network traffic or unrelated UI code will get in the
way. If you aren't careful about this, you will end up testing these
other resources instead of your goal. And when testing against tables
in SQL Server, it's especially important to be careful given SQL
Server's caching mechanisms. So when testing using tables, I'll always
throw out the first few test runs, or even restart the server between
tests, in order to control the cache in whever way is logical for the
feature being tested.
Before getting to my own tests, I'd like to jump off on a quick
tanget. COALESCE vs. ISNULL? Who cares! This isn't a performance
question, this is a question of standards-conformant vs. proprietary
code. ISNULL is non-standard and provides less functionality than
COALESCE. Yet a lot of SQL Server developers love to use it, I suspect
because it's a lot easier to remember (and spell). So learn a new word
and type two extra characters and you'll end up with more maintainable,
more functional code. Sounds good to me -- which is why I am a big fan
of COALESCE.
But I am still curious... Which is faster?
In this case, no test data is needed. We're testing performance
of the COALESCE and ISNULL functions themselves, not using them to
access data from a table. So the most effective test, in my opinion, is
to run COALESCE and ISNULL a bunch of times each (one million) and see
which runs faster:
DECLARE @i INT SET @i = 1
DECLARE @CPU INT SET @CPU = @@CPU_BUSY
DECLARE @StartDate DATETIME SET @StartDate = GETDATE()
WHILE @i <= 1000000
BEGIN
IF COALESCE('abc', 'def') = 'def'
PRINT 1
SET @i = @i + 1
END
PRINT 'COALESCE, both non-null'
PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)
PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE()))
PRINT ''
GO
DECLARE @i INT SET @i = 1
DECLARE @CPU INT SET @CPU = @@CPU_BUSY
DECLARE @StartDate DATETIME SET @StartDate = GETDATE()
WHILE @i <= 1000000
BEGIN
IF ISNULL('abc', 'def') = 'def'
PRINT 1
SET @i = @i + 1
END
PRINT 'ISNULL, both non-null'
PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)
PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE()))
PRINT ''
GO
DECLARE @i INT SET @i = 1
DECLARE @CPU INT SET @CPU = @@CPU_BUSY
DECLARE @StartDate DATETIME SET @StartDate = GETDATE()
WHILE @i <= 1000000
BEGIN
IF COALESCE(null, 'abc') = 'def'
PRINT 1
SET @i = @i + 1
END
PRINT 'COALESCE, first column null'
PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)
PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE()))
PRINT ''
GO
DECLARE @i INT SET @i = 1
DECLARE @CPU INT SET @CPU = @@CPU_BUSY
DECLARE @StartDate DATETIME SET @StartDate = GETDATE()
WHILE @i <= 1000000
BEGIN
IF COALESCE(null, 'abc') = 'def'
PRINT 1
SET @i = @i + 1
END
PRINT 'ISNULL, first column null'
PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)
PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE()))
PRINT ''
GO
You'll notice that I'm not using STATISTICS TIME to get the CPU and
run time. Unfortunately, STATSTICS TIME returns once per statement, so
it is not usable for this test -- we would wind up with one million 0
millisecond results. If you're running on a quiet server (and you
should always run targeted performance tests on a quiet server; that
may have to become Adam's Number 2 Rule if I can't think of something
better) @@CPU_BUSY will give a close enough approximation of how much
CPU time the test is using. And DATEDIFF will give us a good enough
time reading. Note that the predicate in the IF statement will never
return true, so we know that we're not testing our network or client.
I ran these tests several times on a few different servers, and
ISNULL appears to pretty consistently out-perform COALESCE by an
average of 10 or 12 percent. But that's the difference between 6
seconds and 5.3 seconds (the approximate average runtimes per test on
my servers), over the course of a million exections. Hardly worth the
functionality and standards compliance sacrifice, at least in the
scenarios I use these functions for.