Be aware when using ISNULL

ISNULL vs COALESCE in SQL Server

In our T-SQL programming, we mostly use ISNULL function to replace the null value of a column with another value. The same can be achieved using COALESCE function too. In this blog post, we are going to see a significant difference between NULL and COALESCE functions. We are going to discuss about the implicit conversion used by ISNULL and COALESCE functions.

What is COALESCE:

Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

Thus COALESCE gives a substitute value for NULL values from the given list of columns. It always returns the first NON-NULL from the list.

Syntax : – COALESCE ( expression [ ,…n ] )

COALESCE vs ISNULL

When we use ISNULL to get the substitute value for any NULL value it implicitly type casts the substitute as per the checked column or value type. But the COALESCE does not.

Example:

DECLARE @ch_ToCheck CHAR(1)
SELECT ISNULL(@ch_ToCheck, 'XYZ')
GO

Now from above query we are expecting to get ‘XYZ’ as the value, but it will return ‘X’ and not ‘XYZ’. Because the type of the checked Value is CHAR(1), it will implicitly cast the substitute as CHAR(1).

Now try the same with COALESCE:

DECLARE @ch_ToCheck CHAR(1)
SELECT COALESCE(@ch_ToCheck, 'XYZ')
GO

And you will get ‘XYZ’.

Conclusion

With the help of above example, i just tried to explain the type casting feature of  ISNULLs with COALESCE and I am not saying that you should completely avoid the use of ISNULL . Just keep in your mind this significant difference.