T-SQL unexpected behavior in isnull function

Question

When i run this query i expect the result will be 'false'

IF isnull(0,'') = '' 
      select 'true' 

else 
     select 'false'

But sql Server tells me 'true', why?

Answer 1

In this case ISNULL(0,'') returns an integer. SQL Server will cast the second argument to an integer too, ie 0. So 0=0, hence the result is TRUE. Comparing directly to 0 would also return true:

IF 0 = '' 
      select 'true' 

else 
     select 'false'

Using ISNULL and NULL like this is unusual. An ISNULL(someColumn='') function in a WHERE clause would prevent the optimizer from using any indexes that covered someColumn thus forcing a scan instead of an index seek.

Using IF statements in SELECT is impossible. Even in CASE statements, it's better to explicitly check for NULL than apply such transformations.