Default Value or Binding in MSSQL2008 good practice?

Question

If you have for instance a datetime datatype set on a column in MSSQLServer2008 that gives you the datecreated, is it better to have the .NET layer pass the current date or set the Default Value or Binding of the column to (getdate())?

Answer 1

I think it's better to have SQL server handle the date for items like that. If the client passes the date, then you could run into issues if the client's are in different time zones or if one client has the wrong date set on their machine. Plus, it's one less item you need to send in the command.

Answer 2

My view is it doesn't make much difference where you do it, as long as the method is consistent.

I doubt it makes much difference at all in terms of performance, or maintainability.

My preference is to create it in the database with getdate(), however this has caused issues in the passed when using an ORM.

Answer 3

I would create that column with a default value of GETDATE(), but I would have the stored procedure set the actual value. I think it is bad practice to have INSERT statements that do not include all columns.

When appropriate, I like to set a variable @RunDate to GETDATE() at the start of the transaction and mark all CreateDate and LastChgDate columns with @RunDate. I never use dates in Primary Keys, so if they are off a little or the same, it doesn't matter. I feel it is better to see everything the user created/changed at the same time.

Answer 4

You should consider writing the times in UTC instead of server local time. This way if you have to move the server from one timezone to another you don't have any issues.

As an example we are moving our servers from Texas to California. The main production app uses UTC time for all datetime values. However the ticketing system uses server time, and the ticketing app changes the values to the users local time, assuming that the server is in Texas. Everything will get very confusing when we move the server to California and the values are all 2 hours off.