For me, yes, the pump has been that great. But it doesn't meant that it's "that great" for everyone. There are plenty of folks who find it far more beneficial to stick with MDI. I think it depends on what you're looking for, your lifestyle, what you need, etc. Everyone is different and I think it's great that we have options.
Onto your questions.
1. When I sleep, I don't even notice the pump anymore. I did for the first few weeks, but once I got over the fear of ripping it out, it just became a nonissue. I clip it to my shorts or just let it float around in the bed. No pump accidents so far.
2. I disconnect for sex. Like everything else, you (and your partner) get used to it. In fact, asking if I'm disconnected has turned into a kind of, um, pre-sex joke (as in, "So, uh, are you disconnected?") If you have a good relationship, your partner will understand. First and foremost, you need to do what you need to do to keep yourself healthy.
3. Yeah, this part does annoy me a little. I love to swim, and I do disconnect when I want to go in the pool. However, I can really only stay disconnected for about an hour. The Ping is waterproof, but I still would not trust it being submerged in water. Many people find that, if they are careful, they can disconnect for upwards of 2 hours (with some testing inbetween, but you would do that anyway!)
4. My boobs are pretty small too (A-almost-but-not-quite-B cup) and I have no trouble stashing my pump in my bra. I wear those not-really-bras (they kind of look like half t-shirts) and I put my pump between my boobs. The Revel (the pump I use) is small enough that no one notices. There are also leg holsters you can get, but I like between the boobs better (easier access).
Yes, pumps do cost money. They are an immediate expense (for the pump itself; insurance covers most of it, but I had to pay about $900 out of pocket) and then there are the monthly supplies (infusion sets, tegaderm, reservoirs, etc). Again, insurance covers these things, but it all ends up costing quite a bit more than just insulin and syringes.
If you can afford it, I would say give it a try. You can always go back to MDI.