Booking.com
Best overall hotel search
Booking.com consistently searches the most sources and fetches the lowest prices. They also get brownie points for being honest: taxes/fees are included in their prices while others hide it in hard to read text. Good guy Booking.com. However, they do use a lot of Vegas-like flashing signs to get you to book, which can be a bit annoying.
Trivago
Great for further price shopping
Trivago takes a different approach by searching multiple hotel third party sites including Booking.com to give you an ultimate at glance view of rates (called "metasearch"). Results are generally good but the prices won't be significantly different from similar services. They do have one of the most complete set of filters and use effective visual cues like smiley faces and colors for indicating rating.
Hotel Tonight
Best for last minute deals
Hotel Tonight was a revelation in last minute hotel deals when it launched in 2010. Although many other services have caught up in this area, Hotel Tonight still offers the best last minute deals in the most well designed package having started mobile only. They also offer a straightforward 'HT Perks' program that helps offset losing out on the loyalty points you get when booking directly with a hotel.
Priceline
Best for "name your price" type deals
The basis of Priceline's "blind" deals is 1) until you actually buy, you don't know the exact hotel you're getting only the general area and service level and 2) they're non-refundable. It's slightly risky but that's how they offer better prices. There's two types of "blind" deals: "Name Your Own Price" and "Express Deals". "Name Your Own Price" is where you bid until a hotel is willing to take what you pay, but you risk potentially bidding too high. "Express Deals", which they promote more now, are supposed great deals without needing to bid.
You can sometimes figure out the exact hotel they're showing you by matching up details like ratings and price, but it's not a guarantee. Overall, if you love bargains and have the time to investigate, these blind deals can be worth it.