Andy,

I think this may come down to personal preference and agreed standards. Perhaps a little like the naming convention discussions I'm sure some people will have much stronger views than others, however my 2 penneth worth:

- I like defaults for things like dates

- I can see how setting a default for a lookup value may initially look useful but projects that I have worked on previously have regularly seen the business rules develop to an extent where a single default value cannot be specified and it must be calculated based on other criteria. In this instance defaults must be moved back to the business layer/application

- Defaults, a little bit like triggers, are not overly "visible". By that I mean they are not obvious by looking at a table and it's columns. No biggy but it requires the person using the table to dig a little further. Similarly specifying the defaults in the stored procedures/tsql enforces this visibility but loses the benefits (and potentially breaks) the defaults specified.

Overall I'm probably caught between options 2 and 3 in your list and I think the realistic answer is that it will vary by organisation and project, however the most important thing, as always, is consistency. Get a consistent approach, certainly within a project, and preferably within an organisation and as long as all people working on that project adhere to it things won't go too far wrong.