At what level do you prefer to handle Null values in your application?
My personal preference at the moment is the DB. When I build a view, I ensure that I return a value in place of a NULL value. So if your front-end app were to connect to the application it would never have to handle nulls.
In my VB6 apps, I made liberal use of the IsNull function to handle Nulls.
Share your experiences.
This is one of the problems with nulls. Whenever the database developer puts them into the database the application developer has to take them out again. Very often the best policy is to avoid or minimise the use of nulls in your base tables to start with. If nulls are used then it's a good idea to record somewhere exactly what the null is supposed to mean and why it is used. You can then put logic in a view or stored procedure to eliminate or replace the nulls as appropriate.
While there's definitely no one-size-fits all answer to this question, I think you're better off leaving the data transparent. If you're handling the nulls in the database and never returning them to the application, then what is the point of storing null values at all? This seems analogous to always returning the value of an integer column + 1.
This is not to say that null should be used liberally throughout the application. If your select is simply doing IsNull(FieldName, ''), why not simply make the column non-nullable and store the empty string instead? You'll make your data more closely match your business logic and may well make your selects faster (certainly if you're filtering on that column).
I've once seen a list (can't find the referece, alas!) of 13 different possible semantics for NULL, varying over meanings as Unknown value, Conflicting values, Not applicable, Empty, Default.
If you try to process all of those meanings in the database, you end up with a great deal of business logic down there. Conversely, for some meanings, it can be useful to isolate the business logic layer from database internals by e.g. providing a default value. It really depends on what problem you are addressing, and what this particular kind of NULL value is intended to mean.
I used to do like what you describe, use ISNULL(Column1, '') or similar in the view or stored procedure so that I never (or rarely) had to deal with a null in the application. But I found that sometimes a NULL is just the most appropriate way to represent an "unknown" state, even inside the application. So unless I have a specific reason for the "ISNULL" type of logic, I now pass those NULLs down the pipe. In fact, I've tried to get all of my database logic as absolutely pure as possible (simple SELECT, UPDATE, INSERT, or DELETE) statements with as little logic as possible. So, I got over my fear of NULLs. Your mileage may vary!
The problem with NULL is that it is not very descriptive. For example, is the data missing but applicable or missing and inapplicable? You may be better off, from an application perspective in having the DB (via a stored proc, for example) tell you why data is not available, so that your application can take the appropriate action.
I find it really depends on business logic. Passing NULL to a UI field that will simply display as blank is usually OK. Sometimes NULLs have to be handled in the database at the constraint level for fields where NULL cannot be allowed. Sometimes it might be handled in the middle, if the database is accessed by multiple apps - some that are OK with NULL and others that are not.
If you are asking this with regard on how to handle null within an application as a value which is unacceptable; the answer is every place where null could pose a problem.
Defensive programming is often overlooked and assumptions are made by developers that layer N will deal with validation of data and ignore the validation of the data. Unless a forced contract is in place or isolation of the code allows for flexibility in the approach you should always protect yourself from null values and/or incorrect data.
We all know what happens when you assume...
NULLs have their place. Say you have the column [FoodExpiresOn] DATETIME NULL in dbo.[FoodProducts] table. A NULL value (a twinkie never goes bad) is just as significant as a non-null value.
