I worked for Enterprise Rent-a-Car for a year, and learned a few things. Number one, is that your province/state/country’s rules should be known first. The insurance rules are different between each, and can affect what decision you should make.
Generally speaking, however, it depends on what you are renting, how long you are renting if for, and where you are going. In many ways the Collision Damage Waiver (or whatever the rental company is calling their coverage - it’s not insurance) is a bit of a scam, but one with an upside. If you get in an accident, even one where you are deemed at fault, and even if you completely total the vehicle, you will be covered completely by their waiver. So like Yllaria mentioned, you fill out a form and walk away, never have to worry about it again. There are exceptions, of course, such as if you deliberately try to damage the vehicle, or certain types of “discount” waiver that have a deductible, but generally the waiver is the most hassle-free way to avoid extra costs from an accident. The other upside is that the rental company will cover your costs if you need to rent a new vehicle because the one you had can’t be driven, or will cover other means of getting you home if you are out of town (at least Enterprise did). This is best if you are travelling because if you get in an accident without the waiver, you’re on your own and have to make sure that car gets back to the rental place.
That said, if you are not travelling, and in particular, if you are renting a car for an extended period - by which I mean more than 2 or 3 days - it becomes less appealing because of the total costs. The waivers now are running up to $30 a day, plus the cost of the rental, which can be a real expense. You really want to have some way to insure the rental car though, so you need to make sure if you are not taking their waiver that your policy will cover the rental. Check your policy to make sure (most do cover it now from what I understand), and also check to make sure what stipulations there are on extended coverage of a rental vehicle. Sometimes the policy will only cover up to a certain value of vehicle, such that if you own a Honda Civic and rent a Cadillac you may not be fully covered, and sometimes you are guaranteed to have to pay a deductible and have your rates raised even if you weren’t at fault for an accident in a rental. At least that was our understanding here.
As for credit cards, a few things deserve mention. First off, make sure your card has a rental car coverage policy, and find out up to what value and what type of vehicle it will cover. Many will only cover up to a certain value of vehicle, and many will not cover trucks, or 4x4s for example. Also, the rules here and probably elsewhere were that if you wanted the card company to cover the vehicle, the rental had to be paid for by the credit card itself. Otherwise tough luck. The reason was that the card company will take the chance on covering you because they are making money off you using their card. So if you are getting a rental car as an insurance replacement because your own car is damaged from an accident or whatever, and the insurance company is paying for the car, you cannot use your credit card coverage to cover the vehicle. Also, in our experience with the credit card companies, they are notoriously bad at paying on time, and the customer who rented the vehicle is completely responsible for what happens to the vehicle while it is in their name, so if there is damage that has to be repaired and the credit card company or their insurance company or whoever does not pay on time, the rental company is free to charge the customer the full cost. In some juridictions (including here) that also includes the cost of the car being on rent to the customer for the entire time it is out of commission. So make sure you know everything about your options in advance.
So to sum up (sorry, this is way too long), if you have a short rental (1, 2, maybe 3 days) take the CDW, especially if you are leaving town. It’s the easiest way to get off scott-free whatever happens. Otherwise, make sure you know your own insurance policy well, and/or your credit card policy well if you are paying for the rental with that card.
Incidently, I got out of that game because the rental comanies (at least Enterprise) use some shady methods of getting people to take the coverage (because it’s almost entirely pure profit), and I couldn’t take it anymore. Plus I hate serving people.