Auto-generated values for columns in database

Question

Is it a good practice to initialize columns that we can know their values in database, for example identity columns of type unique identifier can have a default value (NEWID()), or columns that shows the record create date can have a default value (GETDATE()).

Should I go through all my tables and do this whereever I am sure that I won't need to assign the value manually and the Auto-generated value is correct.

I am also thinking about using linq-to-sql classes and setting the "Auto Generated Value" property of these columns to true.

Maybe this is what everybody already knows or maybe I am asking a question about a fundamental issue, if so please tell me.

Answer 1

I prefer to assign defaults to my database columns always unless I need to be able to accept blanks. I do this to simply my queries. For example do not allow null bits if you are never going to accept nulls.

Answer 2

Personally, for field that you seldom need and NULL is an option, you should always set the column to allow NULL and dont set it with any default value in your DB or code. The reason I say that is that NULL sometimes have semantic meaning to it where it says nothing was set before. For example I used to work in medical domain and take BloodType as an example, leaving it as NULL means that I haven't collected that information and dont use that in any equation or diagnosis, versus if you give it a random and fed it some calculations and you may end up with some unexpected results. Same can be with string like for observation about the Condition, NULL may mean that no observation decision was ever made, versus a default value that may be incorrect interpreted in later day.

Answer 3

Yes, I would definitely recommend assigning sensible default values to your table columns.

If you have e.g. some BIT fields that are "0" or "1" most of the time, specfying a default makes sure they're initialized to their most likely state, and only if you really need to change them, you have to do something yourself.

If you have sensible default values for your table columns, you can have very simple initial INSERT statements - just insert what you really need, all the other things are being set to a default value.

The default value also only ever comes into play when you INSERT something, so as soon as you already have a row, you can change it any way you like, no problem.

Also, default values help you be able to define NOT NULL constraints - after all, if you set a column to NOT NULL, you have to make sure it always has a valid value stored in it. If you define a default value and happen to forget to set a value in your INSERT, the default will apply and satisfy the NOT NULL constraint.

All in all: yes, default values are a "good thing" ! Use them where appropriate and where they make sense!!