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.