CODEWITHSUNDEEP
sql-servert-sql
John Stuntz
John Stuntz

Reputation: 33

Best and easiet way to capture time

How can I capture the time at which a record was added to the database - effortlessly. I am using this

create table YourTable
(
  Created datetime default getdate()
)

ANy other alternatives?

Upvotes: 1

Views: 103

Answers (3)

Chains
Chains

Reputation: 13157

Still in the vein of using a default constraint... there are other values you can consider using -- different advantages to each (involving universal time, precision, etc.).

http://msdn.microsoft.com/en-us/library/ms188383.aspx

Also -- consider the size of your data type -- datetime is 8bytes -- you could define the column as smalldatetime and improve that to 4 bytes (or in 2008, just plain old date, which is 3bytes -- though you might actually like knowing the time as well).

Triggers are also an option, but not preferable IMO -- for one thing, they can be rolled-back if any constraints are violated (such as external relationship to a table you just created, forgetting about the trigger -- oops!)

Upvotes: 1

beach
beach

Reputation: 8640

Options:

  1. DEFAULT COLUMN (as you have)
  2. INSERT TRIGGER that updates the column to the current_timestamp

Option #2 is more foolproof as it is always updated with GETDATE(). Using option #1 allows the user to manually override the Created date by specifying it in the INSERT clause.

Upvotes: 1

MrTelly
MrTelly

Reputation: 14865

I think that's the canonical approach, do you have a problem with it?

Other approaches would be using an insert trigger, which is probably slower and slightly more complex in that the code is in two places. Or you could channel all updates via an SP, which would also update the Created field - again that's slightly more complex and easy to circumvent unless your permissions are set carefully.

Upvotes: 2

Related Questions