well there was once a debate on SqlTeam in which i claimed that IsNull is slower than Coalesce when used in same fashion:

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:

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