SQL Tip: To ISNULL() or not to ISNULL()

When we’re working on other people’s code – for troubleshooting or optimization purposes – we see a fair amount of usage of the ISNULL() function. Generally speaking we replace those ISNULL() references with COALESCE().

But why?

There are a number of reasons:

1. First, Microsoft has indicated they may deprecate ISNULL() in a future SQL Server release, so we are trying to avoid having to update a lot of code down the road if that comes to pass.
2. ISNULL() only takes two parameters, and sometimes we have to check more than two locations in a complex JOIN structure to get the value we want. (Sometimes we’re checking like six places…). COALESCE() can take up to 255 parameters.
3. INSULL() takes it’s returned datatype from the first of the two parameters, including it’s length, so if you have a VARCHAR(10) field and a VARCHAR(255), then you may see an unexpected truncation of data if the second field is the non-NULL one.

If the first value you are checking is NULL and the result of a JOIN statement that failed to get a record, then ISNULL() will return a NULL, instead of the second parameter which is (perhaps) not NULL.

On the other hand, there are some reasons to use ISNULL() instead of COALESCE():

Interestingly, COALESCE() is not actually a function. It’s a macro-like structure that is re-written by the query optimizer into a CASE statement. Because it translates:

Into

This has several side effects:

1. If your parameters are expressions instead of data elements, then multiple expressions may be evaluated while the CASE processes, and that may impact the performance of your query. Particularly if they are sub-SELECTs or function calls. ISNULL() is only evaluated once.
2. While CASE generally processes in order of the statements, and thus in the order of the parameters, there are a bunch of exception conditions that might crop up… or might not. This means you might not be able to force a check of regular data elements before a sub-SELECT.
3. CASE checks each data type in your parameters and then returns the one with the highest precedence. If you have different data types in your parameters, you may get an unexpected data type returned and you may get an implied type conversion. Implied type conversions are bad. They hit performance right in the nose and you (the programmer) always want to know they are occurring, and address them in your code yourself, rather than letting the engine do it.
4. A little side note – there is almost always an ELSE at the end of the expanded CASE statement. And it might be ELSE NULL, when you don’t expect it to be.
5. If you are sending this query via a LINKED SERVER entry to another engine, then the CASE statement that expands from COALESCE() may be rewritten differently, causing a nested case expression error if there wind up being more than ten (10) nested CASE expressions.

A Handy Decision Matrix

1. If you need to check more than two (2) data elements, you want COALESCE()
2. If you are checking values found in the results of one or more JOIN(ed) tables, you want COALESCE().
3. If you are defining a PRIMARY KEY constraint, you want ISNULL().
4. If you are defining a computed column, you want ISNULL().
5. If you are attempting to make the return value of a scalar function deterministic, so that it can be indexed, you want ISNULL().
6. If performance is paramount and you are checking two expressions, you want ISNULL().

Best Practice

Now that you know which programming option you need:

If you are using COALESCE():

1. Ensure the data type of each parameter is the same by CAST- or CONVERT-ing them.
2. Review the exception cases in case you are going to encounter one.
3. Order your parameters appropriately. Try and put the most common non-NULL condition first.

If you are using ISNULL():

Ensure the data type of each parameter is the same same by CAST- or CONVERT-ing them.
Ensure the first parameter has the data type length you want.

And…

As ever, if you need help with Meditech DR index creation, reporting, extract or analysis please feel free to give your Iatric Systems Sales Representative a call!

Our Report Writing team can help with Data Repository reporting, NPR report writing, data extract or analysis, and much more. Simply reach out to your Iatric Systems Account Executive or our NPR report writing team at reportwriting@iatric.com to discuss how we can help support your team!