I'll vote with the "it depends" party.
I've come to think of defaults as a sign of "lazy programmer syndrome" (LPS), for similar reasons as noggin. I prefer for the application (programmers) to explicitly state important information, particularly for important values such as object state -- is the user unverified, verified, etc.). If the app doesn't know the state to assign an item, then there's problems ahead. (Robert Davis spelled this out while I was typing this…)
Conversely, some values are best assigned by a central authority: the database. Having all "loaded at" date times set by the same server seems wise, given the number of times I've seen out-of-synch servers (three minutes isn't much, at least until it takes you two hours to figure out why something didn't work right...) So defaults on values like CURRENT_TIMESTAMP [dunno why, but I've always used that instead of getdate()], suser_sname(), and the like are fair game. Of course, there's nothing you can do if the app decides to directly write to that column, unless you want to clutter up the system with easily-forgotten triggers (yep, more wasted debugging time behind that opinion).
One thing you’ve overlooked: nulls work as defaults, and (IMHO) are an indicator of severe LPS. They do have their place, such as open-ended time periods and outer joins, but they should always be avoided unless you know precisely what a null value *means* in that row/column (such as "period ends at unspecified point in the future"). My personal mantra is "NULL is not your friend".
A last two cents: these days I'm undecided on lookup codes. Numerics (1,2,3,4) are traditional, fast, and expedient, but characters (U,V,F,X) can convey useful information quickly. I see two primary considerations, and I have not idea how they fall out:
- Character strings must (?) require extra overhead. When SQL compares "A" with "A", how many processor cycles are consumed for code page lookups, case-sensitive, Unicode considerations, or other ASCII ephemera?
- Which is faster, a lookup table or a CHECK constraint (of the form: [col] in [delimited list])? On small tables (probably never hit three pages of data) I've used up to ten character English status codes with check constraints. (But I wouldn't do this for tables that could have more than 100 rows, for the obvious space and performance reasons.)