Last week we looked at how expressions are evaluated with the ISNULL and COALESCE functions. Whilst we’re in the area it’s worth running through how data types are selected for them too. It might have implications with how you implement them.
The way these functions choose which data type to return differs, so they aren’t direct swap outs for each other, and you can’t simply ‘upgrade’ an ISNULL to COALESCE and expect the same result.
ISNULL
The ISNULL function is SQL Server’s proprietary NULL elimination function. When we use ISNULL(ColA, ColB), it will check the value of ColA and return it if not NULL, otherwise it will return the replacement value from ColB.
When it comes to selecting a data type for ISNULL, it will pick the data type of the first parameter. If the second parameter differs in type, it will attempt to be converted, for example:
SELECT ISNULL(IntCol, 1.5) FROM ...
This will return the value of the integer column IntCol, however where NULL it will return the value ‘1’ due to the decimal value being converted to an integer. Similarly if the first value is a string with shorter length than the replacement value, the replacement will be truncated.
If you’re mixing data types, it will attempt to convert one to another. For example:
SELECT ISNULL(IntCol, '0') FROM ...
If the value is NULL this will return a numeric zero as the string will be converted to a number. This all happens silently as long as the data types support implicit conversion. If they don’t, then we’ll get exceptions raised for the conversions.
When using ISNULL we want to either:
- Ensure the data type of the replacement value matches the value it’s replacing
- Convert the value we’re checking to accommodate a specific replacement value of a different type
Don’t leave it to implicit conversion. Implicit conversion can cause strange behavior.
A couple of obscure points – if you use ISNULL(NULL, ColZ) the data type would be based on ColZ, and if you use ISNULL(NULL, NULL) it will return an INT type. Both of those scenarios however, are useless. Let’s move on.
COALESCE
Next up we’ll look at COALESCE which is syntactically similar to ISNULL but we’re able to chain multiple replacement values to fall back on. The function is a SQL standard available across most platforms. In SQL Server its implemented via a CASE statement by the engine which allows it to continue through the various expressions until it finds a non-NULL value.
When this function selects a data type, it will look at the types of all potential results and choose the data type of highest precedence. For example:
SELECT COALESCE(IntCol, StrCol) FROM ...
The return type here will be an INT as it has a higher precedence. Even though an INT could be converted to a VARCHAR (123 > '123'), the precedence assumes the opposite. This was tough to get my head around in the early days as a developer. As with ISNULL, this uses implicit conversion where supported by the data types.
Advice is similar to ISNULL when mixing data types:
- Identify the return type which you want as the result – particularly if used as part of a
WHEREclause where its compared to another data type - Review each expression and perform explicit conversion where there is a mismatch to the expected output type – even if it supports implicit conversion
By reviewing and handling the conversion manually, you take the guess work away from the result, and also clearly define intent for whoever works on the code in the future.
A final note that if all expressions in the COALESCE are NOT NULL, the result will be too.
Wrap up
In this post we’ve had a brief look at return types resulting from ISNULL and COALESCE functions. Although they have similar functionality, how they pick their resulting data type differs. It’s key to understand those differences to use them precisely.
I’m sure we’ve all seen enough exceptions where implicit conversion has been left to happily work its magic. Until it doesn’t.
The most effective approach to dealing with these is to explicitly convert inputs to ensure commonality across all expressions. This can be helpful as you walk through how the function is used within the wider query to understand if there are other implications, such as using the output as a comparison to another data type.
If you’re interested to read more about these functions check out this post to see how the expressions are evaluated, with additional insights from Erik Darling in the comments.
Andy Brownsword 