I am a huge fan of Canon compact cameras for underwater shots. Relativly cheap cases that go down to 40m. Make sure you get a model that has a WP (Water Proof) case rather than AW (All Weather) case.
Powershot.com has the list.
DSLRs make an enormous difference. Having spent a two weeks working with a SD700IS and my dive buddy shooting a Nikon D80, with wide-angle, Ikelite housing and Flash. The depth of field difference is incredible due to the extra light sensitivity from the larger lenses. Also being able to have a serious flash that can reach 2m is a big difference.
With that said compacts have some very good advantages.
1) Price
2) drag in the water
3) ability to carry them to out of the way spots
4) ability to hang it off your hands and be able to use your hands
5) can get into small spaces, caves, under ledges, under wrecks where DSLRs will not fit.
6) Can travel easily with them on planes
7) Setup time to use them. The cases are reliable and low maintenance (at least the canon ones)
8) weight a full DSLR rig is going to weigh around 7kg out of the water
One of the big missing factors on many of todays compacts is RAW mode. This is a big problem for underwater photography. If you shoot in RAW and you didn't set the white balance correctly you can do it retroactively at home. If you shot JPEG you cannot do this, the data (especially the red channel) is lost forever and you image will just get grainy with post processing. The JPEG compression makes some color assumptions for you and throws data away.
Underwater modes are useful for about snorkeling depths. 1-5m in water. Below that the results are better than nothing, but if you don't want it to look something other than blue you will need to do manual white balance. Getting at this for canon-compact cameras is somewhat tedious but you can set it up at the surface so you only have to press one button when you are under water to set the white-balance manually then another button to take your photo.
Underwater mode does a number of useful things if you just want to take shots. It changes the auto-white-balance algorithm to include more red, it changes the ratio of when to try and use flash vs a higher ISO or exposure setting. It is hand for taking a quick shot of something, but it will not correct for deeper depths, or know about the the amount of particles in front of you.
Most compact cameras will exhibit vignetting (dark corners) in their wide angle setting in the default underwater housing.
The effective range of your compact camera's flash underwater is about 0.5m (19inches). You should also never use the underwater setting when using the flash, the reds will be over-exposed. You should switch to manual and use daylight white balance to get better colors.
If you want to take a look at my underwater photos with compact canon's check out
http://flickr.com/robinslomkowski/tags/underwater.
I find that the canon housing are well made, easy maintenance, and are relatively inexpensive. The canon compacts have a better underwater mode than most competitors. Take a look at
Flickr Camera Search and search for underwater, diving, or scuba to get an idea of the results that other people are getting with a camera you are considering. The old Olympus SP series did great underwater work, but the new line-up doesn't seem to be as good.