TSQL Default Minimum DateTime

Question

Using Transact SQL is there a way to specify a default datetime on a column (in the create table statement) such that the datetime is the minimum possible value for datetime values?

create table atable
(
  atableID int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
  Modified datetime DEFAULT XXXXX??????
)

Perhaps I should just leave it null.

Answer 1

As far as I am aware no function exists to return this, you will have to hard set it.

Attempting to cast from values such as 0 to get a minimum date will default to 01-01-1900.

As suggested previously best left set to NULL (and use ISNULL when reading if you need to), or if you are worried about setting it correctly you could even set a trigger on the table to set your modified date on edits.

If you have your heart set on getting the minimum possible date then:

create table atable
(
  atableID int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
  Modified datetime DEFAULT '1753-01-01'
)

Answer 2

I agree with the sentiment in "don't use magic values". But I would like to point out that there are times when it's legit to resort to such solutions.

There is a price to pay for setting columns nullable: NULLs are not indexable. A query like "get all records that haven't been modified since the start of 2010" includes those that have never been modified. If we use a nullable column we're thus forced to use [modified] < @cutoffDate OR [modified] IS NULL, and this in turn forces the database engine to perform a table scan, since the nulls are not indexed. And this last can be a problem.

In practice, one should go with NULL if this does not introduce a practical, real-world performance penalty. But it can be difficult to know, unless you have some idea what realistic data volumes are today and will be in the so-called forseeable future. You also need to know if there will be a large proportion of the records that have the special value - if so, there's no point in indexing it anyway.

In short, by deafult/rule of thumb one should go for NULL. But if there's a huge number of records, the data is frequently queried, and only a small proportion of the records have the NULL/special value, there could be significant performance gain for locating records based on this information (provided of course one creates the index!) and IMHO this can at times justify the use of "magic" values.