Ask any SQL developer to tell you about what is more exasperating than having to account for dividing by zero and they’ll probably say the word “null”. Dividing by zero can be handled with a simple CASE statement, but unless you’re calculating gross profit % on an item you gave away for nothing, it just doesn’t come up as often. Dividing by zero also throws off an error, so at least you know where you need to deal with it.
Whether you’re doing string manipulations, numeric calculations, or date comparison, a NULL value in any of your parameters just returns a blank result and lets you go on your way without any warning.
Using ISNULL will handle the one-off issues, but on a larger scale, what if you need to read data from a delimited text file, manipulate it, and insert the information into a 300-column SQL table which doesn’t allow null values?
Creating a 300-column staging table which allows nulls on all columns and using BULK INSERT will get the data into a workable format, but we need a quick way to set those NULL values to appropriate defaults without writing hundreds of UPDATE…ISNULL statements before we can upload to a less forgiving format.
The following script will set the values of every column in the staging table to the appropriate non-null default, enabling the data to be inserted into the permanent database table.
