There are often a number of tables in a database that contain a column designed to show when the record was inserted. To do this a default constraint is often added to the column using the GETDATE() or GETUTCDATE() functions. These columns can be useful to find out when the row was added during development and provide a simple auditing functionality. Log tables for example almost always have a column set up in this way.

You probably wouldn't add them to tables with very large row numbers because of the additional storage required, but usually on smaller and mid sized tables they can be very useful. An example might be something like this:

RecordAdded DATETIME NOT NULL DEFAULT GETDATE()

The following script should show you the tables with columns that have default date constraints. This uses the sys.default_constraints system catalog view, which applies to SQL Server 2008 and onwards.

  SELECT 
  t1.name AS ConstraintName
  , t2.name AS Table_name
  , t3.name AS column_name
  , t1.definition AS DefaultConstrainDefintion 
  FROM 
  sys.default_constraints t1 
  JOIN sys.objects t2 ON t1.parent_object_id = t2.object_id 
  JOIN sys.columns t3 ON t3.object_id = t2.object_id AND t1.parent_column_id = t3.column_id
  WHERE 
  t3.system_type_id = 61
  AND
  ( definition LIKE '%getdate()%' OR definition LIKE '%getutcdate()%' )
  ORDER BY
  Table_Name, Column_Name, ConstraintName

You might then make the assumption that this field always contains the date and time that the row was added. However, developers using this table might insert a datetime explicitly that overrides the default value.

insert into logEvent (LogAdded, LogDetails) VALUES ('2000-01-01', 'Event with explicit date');
insert into logEvent (LogAdded, LogDetails) VALUES (GETDATE(), 'Event with explicit getdate');

Developers might also use the getUTCDate() function.

insert into logEvent (LogAdded, LogDetails) VALUES (getUTCDATE(), 'Event with UTC date') ;

The problem with this is that now you cannot be sure that the date and time in this column is consistent with the default value.

If you want to prevent developers from inserting values that are not the same as the default then you can add a check constraint to partner the default constraint. This means that developers explicitly inserting the default value will not get an error but values not matching the check constraint will now get an error. 

ALTER TABLE dbo.MyTestLogTable 
WITH NOCHECK ADD CONSTRAINT CK_TestLogAdded CHECK ( LogAdded = GETDATE() );

In an ideal world you would have security that ensures that the only value inserted into this column is the one from the default constraint. In the real world using a check constraint to partner the default constraint provides a simple and practical way to maintain the integrity of the data.