Some folks are not fans of having NULL values in your data. I am not much of a fan myself, however sometimes (all the time) they are a reality in which we have to deal. When working with NULL there are lots of strange behaviors you have to be sure to manage, for example WHERE x = NULL gets you nothing, whereas WHERE x IS NULL gets you the data you want (which will still depending on your ANSI NULL settings). Not much fun to be had with NULL.

Something else to be careful of is managing queries where you might look at a value in one column, and if it’s a NULL, return the value from another. For example:

SELECT ISNULL(ColX, ColY) FROM MyTable;

This all seems well and good, however, should you run into a situation whereby the data sizes are different between these columns you could run into some data truncation problems that SQL will not warn you about, or provided an error for.

Let’s look at a brief example of this in action. First, just create a temp table which holds a single row (plenty of data for the example).

CREATE TABLE #ISNULLTest
(
C1 VARCHAR(6) ,
C2 VARCHAR(15)
);

INSERT INTO #ISNULLTest
( C1, C2 )
VALUES ( NULL, 'not a string' );

SELECT C1 ,
C2
FROM #ISNULLTest;

Pretty basic. Running this will create you a temp table, with two columns of different sizes, one containing a NULL, and the other a value.

Now if we query the data, looking to replace the C1 NULL value with the value from C2 we would expect to see the full string, however that is not what gets returned…

SELECT C1 ,
C2 ,
ISNULL(C1, C2) AS BadData
FROM #ISNULLTest;

As you can see, the C2 value is being truncated and only the number of characters that match the length of the C1 data type are being returned. You get no warnings or errors from SQL that it’s actually giving you back some bad data here.

What’s happening is that SQL is taking a look at the data type of the first parameter in the ISNULL statement, and that’s what gets returned. This could be a real problem should your software rely on the true value.

Thankfully there are a couple of ways to get around this. We can CAST/CONVERT the C1 column, in the ISNULL statement to make it the same size as the other column, or we can use COALESCE (which turns it into a CASE statement at the optimizer). Either one will give us the right result.

SELECT C1 ,
C2 ,
ISNULL(CONVERT(VARCHAR(15), C1), C2) AS GoodData
FROM #ISNULLTest;

SELECT C1 ,
C2 ,
COALESCE(C1, C2) AS GoodDataCoalsced
FROM #ISNULLTest;

So if you are stuck using NULLs in your database (and I’m pretty sure that you are), this is just another reason to be extra vigilant when querying them.