Coalesce vs ISNULL

The difference between ISNULL and COALESCE?

It is quicker to type ISNULL. Plus COALESCE always takes me a few goes to spell correctly, luckily there is intellisense and I can wait for the color of the text to change.

Well yes quicker to type, but probably going to throw the odd issue because you neglected to read the tiny writing with regards to return type. Stepping over the fact that ISNULL is not ANSI standard syntax, it more importantly uses the data type of the check value.

Compare a VARCHAR(3)  with a VARCHAR(5) and you get? ut oh…

DECLARE @a VARCHAR(3),@b VARCHAR(5)
SET @a = NULL
SET @b = '12345'

SELECT COALESCE(@a,@b) --Returns 12345
SELECT ISNULL(@a,@b) --Returns 123

ISNULL returns a truncated value because it uses the data type of the check value.

COALESCE uses data type precedence, is ANSI standard and can compare multiple values.

Which one should you use?

Extracts from Books Online:

ISNULL (Transact-SQL)
http://technet.microsoft.com/en-us/library/ms184325.aspx

ISNULL ( check_expression , replacement_value )

Return Types
Returns the same type as check_expression.
If a literal NULL is provided as check_expression, returns the datatype of the replacement_value.
If a literal NULL is provided as check_expression and no replacement_value is provided, returns an int.

COALESCE (Transact-SQL)
http://technet.microsoft.com/en-us/library/ms190349.aspx

COALESCE ( expression [ ,…n ] )

Return Types
Returns the data type of expression with the highest data type precedence. If all expressions are nonnullable, the result is typed as nonnullable.

Leave a Reply

Your email address will not be published. Required fields are marked *