>>>Hi,
>>>
>>>I am wondering (want to make sure I won't regret at some point) if setting default to many columns is ok (from the stand-point of good db design practice). For example, I have a table that has 34 columns and it looks like I will have defaults set for 30 columns (some 0, some empty string ''). Is this ok? TIA.
>>
>>In our tables practicatelly all fields have defaults except date fields. The regretful part of it is that we've been using deprecated (not yet) bound types for constants.
>
>The trouble with SQL up to 2008 is that when you ALTER TABLE you can't set a bound default, you have to create a literal default. And if the field was nullable or didn't exist before, and now you want to make it not nullable, you have to have the default right there. Unfortunately, a literal default is yet another record that needs to be written somewhere in the metadata (i.e. yet another table among the sys* bunch), and you can see the trade off when doing larger inserts: if you send more fields, you hog the bandwidth; if you send fewer, SQL has to look up the defaults more often. If there are many different defaults, they take up memory (at least for caching), and possibly take more time to process.
>
>Which is why I have only two defaults (dflt_emptystring and dflt_zero) that I use for everything that's not nullable (including datetimes and bits). At least these take very little space and I guess very little processing power when doing mass inserts. When altering tables, I supply the literal defaults, then drop them (this time SQL doesn't complain, knock on wood :), and replace them with bound defaults. Ugly and detouring, but it works.

The bound defaults are deprecated. Don't use them anymore in the production code.