SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Table Defaults

By Andy Warren,

This article stems from a interesting event on a project I was working on recently. Overall it's about defaults, but as you'll see it's just as much about design principles and communication.

I did the database design consisting of about 50 tables, driven by what I'd consider to be fairly standard design goals:

  • Normalize the design (reasonably!)
  • Avoid nulls as much as possible
  • Date stamp rows where appropriate
  • Provide default values where they make sense
  • etc, etc

I also had a light data dictionary where I documented the purpose of each table along with the column names and their purposes as well. For those that are wondering, I built my dictionary by hand in Word. I have no real objection to using the built in extended properties that can be used for the same purpose, but I didn't have a tool handy to generate a doc from the properties and maintaining the doc manually wasn't a lot of work for a project this size.  I also documented some of the standards to be used and how some of the business processes would work.

Now going back to the design principles, I hope most of you would agree with me at least on normalizing and avoiding nulls. Date stamping (requiring you to track at least the date the record was added to the table) isn't uncommon, but in my experience tends to be under used in table design. I'm not trying to build a full auditing solution as I will typically implement that with triggers once the design has stabilized, I just like to know when rows were added. For the tables where I felt a date stamp was needed I used a datetime data type (arguably in some of these cases smalldatetime would have been sufficient, but we'll save that for another day) and a default of GETUTCDate().

Note: After using getdate() for much of my career, in the past couple years I've started to use getutcdate() instead. For those of you who may not have used them, getdate() returns the local server time and getutcdate() returns universal (GMT) time. If you're running a single server you can use either, though getdate() is probably more comfortable since the time you see in your queries will match the time on your watch. Once you start crossing time zones on multiple servers you'll start to find getutcdate() more interesting.

I also listed 'provide default values where they make sense' as a design goal. For example, in this project there is a business process where users start out as unverified, may get moved to a status of unverified (follow up sent), and hopefully to a status of verified. I prefer numbers over text for this kind of situation, so I had a look up table where:

  • 0 = Unverified
  • 1 = Verified
  • 2 = Unverified (Follow up sent)
  • 3 = Expired (Can be deleted)

In the users table I set the default to zero. It's not that I couldn't or wouldn't ever add a user directly with a status of verified, but in the standard business flow I knew that users would start at zero and by setting it as a default, I could save passing in one value to a stored procedure and also indirectly enforce the workflow I had in mind by making sure users started at the correct status.

I was fortunate enough to be able to outsource some of the early data access work to a contractor I had worked with many times before and I provided him with a copy of my docs. Some time later after that initial work was done I was doing some testing and was not getting the expected results. Digging in to the problem a bit I found that the test row I had added and expected to see was being excluded due to a where clause built around the dateadded column. Looking a little further I finally found the problem in the stored procedure used to add the row, he had explicitly specified getdate()!

Talk about an "aha" moment. My first thought was that using GetDate() made sense in his context. Most of us use it routinely and he had worked with me before when I used it, and of course I had not documented that I was using UTC for dates throughout. So my fault, and easy enough to fix.

But wait, there's more. Even if he had used GetUTCDate, to me it would have been bad coding. The column had a default and it would never make sense outside of some type of administrative action to enter a different value for the date the record was added, so I would have just omitted the column from the insert altogether. A middle of the road approach would have been to use the DEFAULT keyword as the value for the date. The worst part is that if I changed the default on the table, perhaps to reflect a change in the business approach, the stored procedure would still be implementing the original behavior.

I think that we wind up with four possible philosophies:

  • Defaults are set for a reason, overriding is treason! Columns with default values should be excluded from all insert statements
  • Defaults are ok, you can include columns with defaults in your insert, but if you truly want the default value the correct way is to use the DEFAULT keyword
  • Defaults are evil. Most likely you would set the column as not null and force whoever is writing the insert to do the homework required to figure out what a good initial value is or where it should come from.
  • Chaos, where everyone does it however they like on the project!

Aside from avoiding chaos, I'm not sure which to recommend. I've used defaults for a long time and am used to working that way, but is it helpful or confusing behavior? Or is it just a matter of setting the standards up front so there is no collision of philosophies? One downside to not using defaults is you would no longer be able to do "insert into table default values" which I find useful when generating a few rows for testing, but have never used in production code. You could enforce defaults via an instead of trigger, but that seems like a lot of work that you may regret at some point anyway.

Is it a Zen question, or is there really a right answer? I'm looking forward to your comments so that we can figure it out together!

Total article views: 7168 | Views in the last 30 days: 1
Related Articles

Default value binding of columns using Trigger

To make a column's default value from another column's value


DB Design Help



Conversion of default objects to column defaults

This script can convert the usage of bound default objects in tables to column defaults


Where cannot we use "default"

What is that column?

database design