Null Defaults

One of the things that becomes important in a distributed, team environment is the interface that you present to other systems. In a Devops environment, we really need to have an API for every system, including the database. This means a contract for our database, that spells out what access points are available and what they return. For our data, this often means the structure and shape of a table.

Our tables often aren’t static. In fact, they grow and chance over time. This means that the values returned from the table, or even views or stored procedures referencing the table, will change over time. In some sense, this means we’re really versioning our API. If that’s the case, then as you make additive changes, you’ll be adding columns for the most part. The will be time when your application, or maybe a subset of your applications, will not know how to provide data for those columns. In those cases, we need to make a decision about how to handle the column.

What’s your default choice? Do you use NULLs when you aren’t sure? Perhaps you choose some magic value, such as a blanks or a known date? I know some people like to use 1900-01-01 to mean an unknown date, or even a number such as 99999. Any choice has advantages and disadvantages, and what works for one person might not work for another.

Let us know your default choice and why. I’m sure some of you have great reasons for why you choose a value, and I bet more than a few of us learn something about why one value might be better than others. I’m looking forward to reading your thoughts.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.9MB) podcast or subscribe to the feed at iTunes and Libsyn.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Editorial and tagged . Bookmark the permalink.

One Response to Null Defaults

  1. For me, all BIT fields are NOT NULL. To me, they are either 1 or 0. If I need a third option, then I move to a TINYINT.

    If possible, I have all fields that go back to a lookup table have a default value so that NULLs can be avoided. Something like 0=Not Started, 1=Initiated, 2=Completed.

    For dates, I prefer NULL over a fake date, unless I’m building a DW. Then a discussion needs to occur about what does a NULL mean to the business.

    One last thing. During the requirements gathering, I always ask about the nullability of the field. I don’t think all fields should be NULL. I think the least number of fields should be NULL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s