Strange SQL Server Behavior

A co-worker discovered the following odd behavior in Microsoft SQL Server 2000.

declare @foo table (
A int, B varchar(50))

insert @foo values(null,'abc')

select IsNull(A,'-'), B from @foo
select IsNull(convert(varchar(5),A),'-'), B from @foo

The result of the first select statement is the value 0 for the first column. This statement in reality should have caused an error message and indeed does cause an error when the type of the A column is changed to a float. For some reason an integer has an implicit null conversion to the value 0. This may have something to do with the setting of ANSI NULL, I have not investigated it that much.

As a final note, this functionality is the same in SQL Server 2000 and SQL Server 2005.

The following select statement in MySQL returns the value '-' for the column A when in reality column A should be of type Integer!

SELECT IFNULL( A, '-' ) , B

and the following query works without error as well:

SELECT 1,''
UNION
SELECT IFNULL(A,'-'),B
FROM FOO

when the previous query should return an error because an integer type is not compatible with a char/varchar type!

So in conclusion it pays to be aware of this issue as my co-worker discovered. Apparently a significant amount of work was done based upon incorrect data resulting from this odd behavior.

Looking For MD5 or SHA1 Implementation In T-SQL?

For those of you that might be looking for an implementation of the MD5 or the SHA1 implementation for SQL Server 2000 or SQL Server 2005 this might fit the bill!