If this question is too broad, my apologies. I am a DBA and I am working with a developer that thinks column defaults are a bad idea and that just setting the column to disallow nulls is good enough.
I am looking for the benefits of column defaults from the developers point of view. Thank you for your comments.
A default value makes it a lot easier to insert new rows into a table - all columns with a default value do not need to be explicitly specified and provided with a value in the INSERT statement, if that default value is OK (like getdate() for a "LastChangeOn" date column).
I don't see any reason why you would not use default values on a column, really, when there is a valid and reasonable default. I don't see why your developer has such an aversion against default values - they can always be overridden in the initial INSERT (and don't come into play anymore after that).
Marc
It depends on your requirements. If your rules require a minimum value and NULL is not considered a valid option, then a default might be necessary. For example, if you have audit fields you are not going to want a null value in a CREATED_DATE column. However, if you have a MIDDLE_NAME attribute you would want to allow NULLS because not everyone has a middle name. Again, it really depends on your requirements.
If you are adding a column to an existing table and do not want the column to be nullable you must provide a default value.
Many developers do not like to put business logic in the database. The developer may think he is losing control (He could write routine to have his own default to something other than null). I'm not sure if he would see this as doing him a favor/taking some tasks off his plate.
I deal with a database that is a backend to a third-party app and I am limited on what we can do with the application (Like have default values). I can't force the application to have a default on all of the data entry panels, so I would like to put a default on a field in the database where I do have control. I don't have access to their code and our users don't want to wait for an upgrade.
Column defaults allow you to remove a lot of the hassle of creating records from your code or application logic.
Advantage #1: If you have a table with 2 columns of user information and 20 columns of tinyint/boolean fields, let's say they are privacy settings, and you go to create a new record in that table, without a DEFAULT value you're going to have to specify every column in your query. There's probably a common setup that you want these records to have by default, and you can set this up using DEFAULT values. When you INSERT a record, you only need to specify the two user information fields, and voila, your record is created with a nice set of common privacy settings. You can tweak the flags individually after that.
Advantage #2: Forward compatibility! If you have a bunch of INSERTs in your code, and then some columns are added on, you're going to have to go back and modify all those INSERTs if you don't specify a DEFAULT value (assuming, as is common, that NULLs aren't going to cut it). Often it isn't necessary to update old code for a new column (since the old code by nature doesn't care about the new column), so this would be a huge pain in the @$$ if you had to start going back and handling every new column in your code as it came along.
Column defaults allow the programmer to be lazy. That's not necessarily a bad thing.
The developer makes a very good point. As a general rule, it is a good idea to be as explicit as possible and rather than relying on implicit behavior.
I think that he/she may be right. If you put defaults on columns and the code relies on that, then changing the default could later introduce bugs that are hard to weed out.
The converse of this, however, is that putting defaults in the data table itself allows you to change the default for a column at a later time without recompiling the code. That is, if the person writing the code was forward thinking enough to not override the default except when absolutely necessary.
It helps with versioning. For example if old code (INSERT statements) expects a table with 10 columns, and that old code must work with the new table with 12, then you'll need to provide a default value for the new columns, or make the new columns nullable.
And it also depends on the exact semantics of what NULL means for a given column, because a choice of defaults is also a choice for or against NULL in a column. Does the empty token mean unknown, unknowable, not changed yet, something in the unknown past, something in the unknown future. These are slightly different tokens and NULL can only mean one of them.
I think that depending on default values in the DB goes completely against defensive programming and your looking at a future nightmare.
In general, default values are not called for. If the data has some default that makes sense, then go ahead and code that in the database. But most data does not have a default value. What would be the default for first name? Throw on a not null and let the code assign the value. Don't include a default, because you want an error thrown if the code forgets to set first name instead of having a bogus default first name.
One place I found defaults handy was for a simple "Audit" scheme. Every table had four columns: InsertedAt, InsertedBy, UpdatedAt, UpdatedBy, with a not null constraint. Used an after trigger to set the values but SQL Server would check that the columns had values. If not a constraint violation happened and it never got around to the trigger. So I added defaults, because these values were not to be set by the code doing the insert. The defaults were midnight January 1, 1900 and a non-existant user. The trigger would check that the values matched the default, and throw an error if anyone tried to set the values at insert time. (Also, update threw an error if anyone tried to update the audit columns.)
It depends on the situation. There are many situations where default values help people get the data right. Data entry forms often meet the pattern. In data entry forms, the default value can be echoed back to the user for validation before proceeding forward. This often allows the user to override the default, when the default is nonsense.
But a user interface and a programmer interface are very different things. Providing a default value in a a DB is often a way of facilitating the entry of garbage information. Getting rid of garbage information once it's in the DB is labor intensive.
So... I would only set up a default value when the design spec calls for it. And in this case, the developer's opinion should weigh heavily in the design spec.