Having worked on a lot of waterfall projects in the past and a lot of adhoc and agile projects more recently, there's a number of design artifacts I like to create although I can't state enough that it really depends on the details of the project (methodology/team structure/timescale/tools etc).
For a generic, server-based 'enterprise application' I'd want the bare minimum to be something along these lines:
- A detailed functional design document (aka spec). Generally something along the lines of Joel s' WhatsTimeIsIt example spec, although probably with some UML use-case diagrams.
- A software techical design document. Not necessarily detailed for 100% system coverage but detailed in all the key areas and containing all the design decisions. Being a bit of an UML freak it'd be nice to see lots of pictures along the lines of package diagrams, component diagrams, key feature class diagrams, and probably some sequence diagrams thrown in for good measure.
- An infrastructure design document. Probably with UML deployment diagram for the conceptual deisng and perhaps a network diagram for something more physical.
When I say document any of the above might be broken down into multiple documents, or perhaps stored on a wiki/some other tool.
As for their usefulness, my philosophy has always been that a development team should always be able to hand over an application to a support team without having to hand over their phone numbers. If the design artifacts don't clealry indicate what the application does, how it does it, and where it does it then you know the support team are going to give the app the same care and attention they would a rabid dog.
I should mention I'm not vindicating the practice of handing software over from a dev team to a support team once it's finished, which raises all manner of interesting issues, I'm just saying it should be possible if the management so desired.