column default value or including in insert script?

Question

which is better, is there any performance difference? setting datetime column's default value to getdate() or using getdate() with the insert t-sql script.

Answer 1

If you absolutely, positively have to have a default date no matter what application or user is entering the record, then use a column default value. Even if there is a time when the user should pay more attention to this date, you can require it on the front end.

I guess if you plan on spending as much time designing, testing, and debugging your code as the developers who wrote the column default value code in SQL Server, be my guest ;)

Answer 2

It depends upon what you're using that DateTime column for. If this is an "Insert Date" or "Last Modified Date" column, use a column default.

DateTime columns are the source of a lot of confusion/hassle/fistfights between SQL coders and client application developers--this is particularly true if your application(s) will support more than one locale. To the extent that you use default values you don't have to worry about the client developer getting the date format correct (or converting from the user's locale into your SQL Server default locale).

As a general rule, when I specify a column as "NOT NULL", I will either provide a default value, or write up an "expected error" in the documentation. Default values save development time, and make your life easier.

And no, there's no performance issue either way.

Answer 3

Set the column default value. There's always a chance that a DBA or developer will write an insert statement that bypasses your insert script and you'll want to make sure that the value is set even in those cases.