Are there any vices to using default constraints on all columns in your table.
For example an Int that defaults to 0
or a char or varchar that defaults to ''
I know that 0 and Null are not the same thing. But if your programs don't have the concept of NULL then you have to convert the NULL to zero.
So, DEFAULT CONSTRAINTS on every column. Is it good or Bad
Thanks
Darin Clark
My opinion is use them when its appropriate.
For example, if you had a BIT field such as "OkToSendLotsOfSpamTo" then you may wish to have a default on this to say default(0) as in opt out.
However, for data such as a persons gender char(1) it makes no sense to have a default.
HTH!
Thanks for your reply.
In the case of gender though wouldn't an empty field or an U for unknown be simpler for programs to process than NULL would
Darin Clark
I guess that depends. As the value NULL suggests its unknown then, from a DB perspective i see no reason to change it. NULL means unknown across the board- for an INT, a CHAR, a DATETIME etc. If you want to put in your own bespoke logic to cater for unknown values then i guess that is something you can do but in my opinion its added complexity.
HTH!
From a qualitative analysis perspective, there can be major differences between a NULL and zero (0), or 'empty string'.
For example:
A NULL value could indicate that the field has NEVER held a response, and
an 'empty string' could indicate that there has been a response and it was deleted.
Use NULLs where it the NULL value adds meaning to the data, use DEFAULT values in all other fields. NULL values increase code on the development side, with the constant need for NULL validation (dbNULL) before displaying the field value on the screen or report. IF NULL is not required, DEFAULT values ease the 'hassle' factor for the DEV staff.
HOWEVER, remember that DEFAULT zero (0) values may have adverse affects for certain aggregations. Using COUNT(), AVG(), StDEV(), VAR(), as well as some other perhaps desired mathematical capabilites could be substaintly skewed by DEFAULT zero (0) values.
So carefully consider if using a DEFAULT value is a help or a hinderance.
No disagreement with anyone here. I will add two more cents worth
Thanks for everyone's thoughts on the matter.
I am recluctant to mark any one reply as the answer as they all build on each other.
As is the case in 'real' life, usually there is no 'the answer' -but many nuanced shades.
True, but we do need to mark as many of the responses as answers as are good, for other people who do searches 