ISNULL vs. COALESCE speed test in TSQL

original post: ISNULL vs COALESCE speed test

IsNull (col1, 'somevalue') vs Coalesce(col1, 'somevalue')

so i did a little testing. first i inserted 500.000 rows into a table with 5 columns:

create table TestTable (col1 varchar(50), col2 varchar(50), col3
varchar(50), col4 varchar(50), col5 varchar(50))

in which for each 100k rows one column was filled with 50 chars and the rest
were null.

then i ran both of the statements (one for coalesce and one for isnull) 10
times and took 10 elapsed time measures. Out of those 10 elapsed times i didn't
use the 2 that were most deviated from the rest:. Then i put the 2 select
statements into a stored procedure and did the same thing. I didn't take into
account the first run of each sproc because of the execution plan calculation.
What surprised me is that the stored procedure versions were slower by 4 seconds
in average. weird... if any one can explain that i'd be glad to know.

I would really like to know how IsNull works internally, because COALESCE
works like this:

COALESCE(expression1,...n) is
equivalent to this CASE function:

CASE
WHEN
(expression1 IS NOT NULL) THEN expression1
...

WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE
NULL

that is directly from BOL. So my guess is that IsNull has something slower
inside which is surprising acctually.

I ran all this on a box with Microsoft SQL Server 2000 - 8.00.760
(Intel X86), Windows Server 2003, 512 Mb of RAM and 864 MHz CPU

RESULTS:

	coalesce	isnull
No Sproc	23258 ms	27613 ms
Sproc	27255 ms	31962 ms

The whole script:

use northwind
if
object_id('tempdb..#temp') is not null
drop table #temp

declare @var varchar(50)
set @var
= 'fberjhreaugheagh954wz645whla2309563498743wzherusbr'
select t1.col1,
t1.col2, t1.col3, t1.col4, t1.col5
into #temp
from
(
select @var
col1, null col2 , null col3, null col4, null col5 union all
select null col1,
@var col2 , null col3, null col4, null col5 union all
select null col1, null
col2 , @var col3, null col4, null col5 union all
select null col1, null col2
, null col3, @var col4, null col5 union all
select null col1, null col2 ,
null col3, null col4, @var col5
) t1
cross join
(
select @var col1,
null col2 , null col3, null col4, null col5 union all
select null col1, @var
col2 , null col3, null col4, null col5 union all
select null col1, null col2
, @var col3, null col4, null col5 union all
select null col1, null col2 ,
null col3, @var col4, null col5 union all
select null col1, null col2 , null
col3, null col4, @var col5
) t2
cross join
(
select @var col1, null
col2 , null col3, null col4, null col5 union all
select null col1, @var col2
, null col3, null col4, null col5 union all
select null col1, null col2 ,
@var col3, null col4, null col5 union all
select null col1, null col2 , null
col3, @var col4, null col5 union all
select null col1, null col2 , null col3,
null col4, @var col5
) t3
cross join
(
select @var col1, null col2 ,
null col3, null col4, null col5 union all
select null col1, @var col2 , null
col3, null col4, null col5 union all
select null col1, null col2 , @var col3,
null col4, null col5 union all
select null col1, null col2 , null col3, @var
col4, null col5 union all
select null col1, null col2 , null col3, null col4,
@var col5
) t4
cross join
(
select @var col1, null col2 , null col3,
null col4, null col5 union all
select null col1, @var col2 , null col3, null
col4, null col5 union all
select null col1, null col2 , @var col3, null col4,
null col5 union all
select null col1, null col2 , null col3, @var col4, null
col5 union all
select null col1, null col2 , null col3, null col4, @var
col5
) t5
if
object_id('TestTable') is not null
drop table TestTable
create table
TestTable (col1 varchar(50), col2 varchar(50), col3 varchar(50), col4
varchar(50), col5 varchar(50))

go
set ROWCOUNT
100000
go
insert into TestTable
select t1.col1, t1.col2, t1.col3,
t1.col4, t1.col5
from #temp t1, #temp t2
where t1.col1 is not
null
go
insert into TestTable
select t1.col1, t1.col2, t1.col3,
t1.col4, t1.col5
from #temp t1, #temp t2
where t1.col2 is not
null
go
insert into TestTable
select t1.col1, t1.col2, t1.col3,
t1.col4, t1.col5
from #temp t1, #temp t2
where t1.col3 is not
null
go
insert into TestTable
select t1.col1, t1.col2, t1.col3,
t1.col4, t1.col5
from #temp t1, #temp t2
where t1.col4 is not
null
go
insert into TestTable
select t1.col1, t1.col2, t1.col3,
t1.col4, t1.col5
from #temp t1, #temp t2
where t1.col5 is not
null
go
set ROWCOUNT 0
go

create procedure
spTestTableCoalesce
as
declare @testValue varchar(50)
set @testValue =
'this is a 50 char sentence.i have no other idea :)'
select
coalesce(col1, @testValue) as col1,
coalesce(col2, @testValue)
as col2,
coalesce(col3, @testValue) as col3,
coalesce(col4,
@testValue) as col4,
coalesce(col5, @testValue) as col5
from
TestTable
go

create procedure
spTestTableIsNull
as
declare @testValue varchar(50)
set @testValue =
'this is a 50 char sentence.i have no other idea :)'
select
isnull(col1, @testValue) as col1,
isnull(col2, @testValue) as
col2,
isnull(col3, @testValue) as col3,
isnull(col4,
@testValue) as col4,
isnull(col5, @testValue) as col5
from
TestTable
go

-- this is so my disk space doesn't
go to zero...
DBCC SHRINKDATABASE ('Northwind', 10)
go

select count(*) as RecordCount from
TestTable
go

set statistics time
on

--ran 10 times
declare @testValue
varchar(50)
set @testValue = 'this is a 50 char sentence.i have no other idea
:)'
select coalesce(col1, @testValue) as col1,
coalesce(col2,
@testValue) as col2,
coalesce(col3, @testValue) as col3,

coalesce(col4, @testValue) as col4,
coalesce(col5,
@testValue) as col5
from TestTable
go
--ran 10 times
declare
@testValue varchar(50)
set @testValue = 'this is a 50 char sentence.i have no
other idea :)'
select isnull(col1, @testValue) as col1,

isnull(col2, @testValue) as col2,
isnull(col3, @testValue)
as col3,
isnull(col4, @testValue) as col4,
isnull(col5,
@testValue) as col5
from TestTable

--ran 10 times
exec
spTestTableCoalesce
go
--ran 10 times
exec
spTestTableIsNull
go

drop table #temp
drop table
TestTable
drop procedure spTestTableIsNull
drop procedure
spTestTableCoalesce