As with all products, the use of the camera really depends on what you buy it for; as a consumer model, this is probably somewhat high end, but I wouldn't consider it professional grade. It has a rich set of features that you definitely wouldn't find on less expensive models.
The CD function sold me on the camera, since I am an avid Linux user and didn't want to fiddle with attaching a USB card reader to my system. (Support for them has gotten better in the six months since I purchased the camera, but I have to admit that I'd still stick with the CD's.) The cost to picture ratio is incredibly low--you can buy mini-CD's cheaply (CD-RW's are more expensive), and they can easily hold 300 pictures each (1024x768 resolution, fine). The camera does allow you to take uncompressed images (TIFF format) if you want.
As for endurance, the camera has done very well. I did drop it (from about three feet) once, which broke the UV filter I had on it. The camera itself sustained no damage (it was in a bag). The battery life is around two hours, depending on flash use, how often the power is cycled, and whether you spend more time taking pictures or looking at them on the LCD (which, by the way, is of excellent quality; Sony has done us the favor of allowing us to zoom in on the image while still in the camera, so you can often tell how well you've focused on a shot before you open it up on your computer).
The picture quality is good. Not perfect, but definitely good. I bought it mostly for web work rather than printing my own shots, which aren't anything to brag about. I have found that taking pictures at 1600x1200 leaves the photos a little grainy (I run my monitor at 1024x768). Its automatic adjustments for light and distance are usually pretty good, though I occasionally mangle them a little bit in manual mode. The ability to take movies has been slightly beneficial, but probably isn't a major selling point (I've used it for some analysis in the martial arts for board breaking).
Some problems can arise when taking and recording pictures, which can be a little slow. Recording to mini-CD is certainly slower than recording to a stick, and the focusing process isn't the fastest in the world. (There is a manual focus button, which is convenient.) Depressing the action button down about half way will activate the auto-focus and allow you to take the shot at the right moment, but that does take some getting used to.
For people who are going to take a lot of pictures, this is definitely a camera to consider. I've taken probably two thousand of them without any difficulty since I got the camera. The resolution may not be as high as you could get in a stick model, but smaller prints should help mask that a little bit. It's a little pricey, but comes with a plethora of options that can introduce novices (like me) to manual photography without the expense or hassle of development.
And, if you like the special side gadgets--TV out and a USB connection (if you even need to use it)--this could turn out very well.