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.

Comments are closed.