isnull(email,'') = '' is NOT interpreted by it self as (email is null or email = '')?

Question

Hi, as a fact of performance which one is better? Is there a difference between the actual 3 three versions of sql-server (2000 / 2005 / 2008)?

Answer 1

If you're going to see performance differences, they'll be minute.

I believe the preferred style is

ISNULL(email, '') = ''

Answer 2

You definitely want to avoid the use of any custom or built-in functions wrapping a column in a filter - it severely limits what the optimizer can do for you in terms of index usage and seekability. You should get in the habit of using equality operators and or/union approaches when possible, as is the case here. The following would be much preferred over a isnull() or coalesce() approach:

where (
   (t.email is null)
   or
   (t.email = '')
  )

or a union approach as outlined below could work better as well, try it out in your environment to determine which option is best.

A simple example will demonstrate the drastic differences you can see in performance:

use tempdb;
go
if object_id('tempdb..#testTable') > 0
 drop table #testTable;
go
-- Build the dataset
select top 10000000
  cast(cast(a.name as varchar(100)) + '@' + cast(row_number() over (order by a.object_id) as varchar(15)) + '.com' as varchar(150)) as email, 
  row_number() over (order by a.object_id) as id
into #testTable
from sys.columns a
cross join sys.columns b
cross join sys.columns c
go
-- Create some nulls
update #testTable
set  email = null
where id % 1000 = 0
go
-- Index
create unique clustered index ixc__dbo_testTable__temp__nc1 on #testTable (email,id) on [default];
go
set statistics io on;
set statistics time on;
go
-- Try with isnull - ~cost of about 44.7 on my machine, ~2900ms to execute, and about 49,200 logical reads
select *
from #testTable t
where isnull(t.email,'') = '';
go
-- Try with 'or' - ~cost of about .049 on my machine, ~643ms to execute, about 31 logical reads
select *
from #testTable t
where (
   (t.email is null)
   or
   (t.email = '')
  );
go
-- Try with union approach - ~cost of about .054 on my machine, ~751ms to execute, ~30 logical reads
select *
from #testTable t
where t.email is null
union all
select *
from #testTable t
where t.email = '';
go
if object_id('tempdb..#testTable') > 0
 drop table #testTable;
go