What exactly is a NULL?

Published On: 2009-02-23By:

NULL values are something that a lot of people just don’t understand the use off.

I often come across databases when instead of using a NULL value some known static value is put in instead.  For dates its usually 1/1/1900, for numbers -1 is quite popular, for character fields a blank value is great.

The biggest excuse for not using NULL values in a database that I see is that the client programmer doesn’t want to have to write an IF statement around the database column in case it is NULL, so the use some other column.  What happens later on, they write some code around the default value so that it doesn’t display if that value is in there because the business unit doesn’t want to see 1/1/1900 for a deal close date in the purchasing system if the purchase hasn’t closed.  So instead of changing the database value to NULL the database is left with these useless meaningless values in it.

NULL means unknown.  Not knowing the value is many cases is perfectly acceptable, and should be treated as such.  If not knowing the value isn’t acceptable, then don’t accept the record without the values.

Now, the trick with NULL is that is also means nothing (it can really mean pretty much what ever you want, but I’m sticking to the more basic thinking here) as well as unknown.  For a lot of programmers this is a tough concept to grasp.  They always want to know what all the values are, and unknown just doesn’t fit into their idea of knowing all the values, so they try and stick an value in there.

I do my best to work with programmers to get those values corrected, and get the systems designed so that they have the correct values in the tables and so that the values are being shown correctly.

The database gives us the NULL value, we should use it when it is the correct value to use.

Denny

Contact the Author | Contact DCAC

3 responses to “What exactly is a NULL?”

  1. Wqwetto says:

    How is NULL more meaningful than 1/1/1900? What is a meaning without a model of the data? I can define a model in which NULL dates are interpreted as 1/1/1900!

  2. Whereami says:

    Hey Mr. Denny – Thanks for this explanation. It helped me understand [B]Null[/B] a little better. I’m not a sql programmer and I’m not a dba so things like this help me out. Past couple of years I’ve had to learn some sql stuff on my own as I’ve been main support for a 3rd party software application that uses sql 2000 databases and a .NET frontend application.

    Just yesterday I came across a problem which I hope you can help me with.

    I have searched the web for “null value in text field”, “null”, etc.
    There are pages and pages of search results and I’ve been reading some of the links that look promising but so far have not found anything useful.

    [B]Can you please help? I think the solution is probably pretty simple but I just don’t know sql well enough.[/B]

    Below is a description of what I’m trying to do:

    I’m trying to load a text file using a DTS package into a SQL 2000 database table. The text file contains information from our SAP backend system.
    All the text file fields have a fixed length, and the fields are delimited with the vertical bar ‘|’.

    1 of the fields in the sql db table is defined as a datetime with length 8.
    This table date field allows null.

    This is my question:

    [B]If my date field in the text file is blank or unknown, how can I move a null value into the date field so that DTS can process the field into the sql database table correctly?[/B]

    Things I have already tried:

    00/00/0000 in the date field – – > this does not work.

    blank in the date field – – > this does not work.

    09/09/9999 in the date field – – > this works but now the front end shows this fake date.

    getting rid of the spaces between the vertical bars if there is no date value
    such as this – – > || < – – this works but I don’t know how to move the vertical bars if the field is blank.

  3. mrdenny says:

    Whereami,
    Sorry to be so long getting back to you.

    The best bet is to load the data into a staging table which has the date field defined as a text string. You can then load into the staging table using the native value, then insert into your production table casting the value to a datetime data type. You can use a CASE statement to convert the value that SAP is giving you into NULL.

    Denny

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers    VMWare Partner
Microsoft Certified Master    Microsoft MVP
Share via