[unable to retrieve full-text content]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:

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).

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…

Continue reading on SirSQL.net.