Question: One of our programmers tends to use isnull in MS SQL to compare with NULLs.
That is instead of writing `Where ColumnName Is Null` he writes `Where IsNull(ColumnName,0)=0`

I think that the optimizer will convert the latter into the former anyway, but if it does not - is there a way to prove that the latter is less effective, since it 
1.Compares with null, 
2.Converts to integer, 
3.Compares 2 integers
instead of just comparing with null.

Both ways are really fast for me to be able to use the execution plans (and also I think, that the optimizer plays its part). Is there a way to prove him that just comparing with Null without IsNull is more effective (unless it's not).

Answer: 
Another obvious issue is the `ISNULL` precludes the use of indexes.

Run this setup:

```
create table T1 (
    ID int not null primary key,
    Column1 int null
)
go
create index IX_T1 on T1(Column1)
go
declare @i int
set @i = 10000
while @i > 0
begin
    insert into T1 (ID,Column1) values (@i,CASE WHEN @i%1000=0 THEN NULL ELSE @i%1000 END)
    set @i = @i - 1
end
go

```

Then turn on execution plans and run the following:

```
select * from T1 where Column1 is null
select * from T1 where ISNULL(Column1,0)=0

```

The first uses an index *seek* (using `IX_T1`) and is quite efficient. The second uses an index *scan* on the clustered index - it has to look at *every* row in the table.

On my machine, the second query took 90% of the time, the first 10%.
IsNull is not well used if you are using it in the where clause and comparing it to 0, the use of isnull is to replace the value null

<http://msdn.microsoft.com/en-us/library/ms184325.aspx>

For example:

```
SELECT Description, DiscountPct, MinQty, ISNULL(MaxQty, 0.00) AS 'Max Quantity'
FROM Sales.SpecialOffer;

```