SQL - Now you see me, now you don't

Introduction

SQL Server, undoubtedly my favourite Microsoft product, but a seasoned baseball pitcher will be impressed by the curve balls it can produce.

Using the code 

Execute the following script:

declare @var1 VARCHAR(1)
SET @var1 = '2'
SELECT ISNULL(@var1, 1)
SELECT ISNULL(@var1, -1)
SELECT COALESCE(@var1, -1)
SET @var1 = NULL
SELECT ISNULL(@var1, 1)
SELECT ISNULL(@var1, -1)
SELECT COALESCE(@var1, -1)

Sure, it is easy to see the culprit, but now read this thinking that @var1 is a column defined in a table, so it is not so obvious what the length of the column/variable is.

• ISNULL – replaces the NULL value with the given replacement.
• COALESCE – returns the first not NULL expression in the argument list.

When reading the MSDN topic on T-SQL ISNULL it does say that it returns the SAME type as the checked expression, the value is IMPLICITLY converted to the checked expression’s type when the two types differ.

The interesting part is that the conversion does not procedure a String or binary data will be truncated error?

This does not prove that COALESCE is better than ISNULL, all it is saying: know the animal you are working with.