I slept in a Sleep Number bed several years ago while in a hotel on business. I cranked it up as firm as it would go and slept very well, but hadn't justified spending the $$ to replace my coil mattress at home.
My hubby slept on a Tempurpedic mattress while at a friend's cabin four years ago and really really loved it. I was (again) skeptical about spending the $$.
Last month we decided it was time to replace our ten year old coil mattress. We went to Sleep Train to just look around and see what was available. The sales rep recommended we go for a slightly higher coil count b/c my husband is a bigger guy, so we were looking at spending around $1000 for a mattress that would (hopefully) last us ten years. We then tried out the entry level Tempurpedic mattress and I have to say it was amazingly comfortable! When we heard that they had a special going on then that meant we could get the mattress and base (which have a 20 year warranty!) for $1650, we decided it just made sense to go for it. It'll end up being cheaper per year, and we should sleep better. I also liked that the pricing is set on the Tempurpedic - yes, it means you won't be able to find a deal at a discount mattress shop, but you won't have to fuss and fight and try to find a comparable mattress for less so you can be sure you're getting the best deal!
So, now that we've had it for almost a month: we LOVE it. It is amazingly comfortable. Admittedly it's a little weird at first to plop down on it and not have it bounce like a coil mattress does, but once you get used to it it's wonderful. There was a very slight odor when they first unwrapped it, but once you get the mattress cover and sheets on it you don't notice it. I suppose you might have more of an odor problem if you bought the pillows, but we just kept using our old ones. (Incidentally, my ears are jealous of the rest of my body b/c they get smushed by a regular pillow every night instead of being cushioned by memory foam!)
I am seven months pregnant so sleep is at a bit of a premium, and I find that even though I am still waking up regularly in the night to go to the bathroom I am feeling more refreshed and less wiped out during the day - I attribute this to sleeping much more deeply when I am asleep. I've noticed that I wake up in the same position I started in, which is another sign that I'm sleeping deeply and not tossing and turning. I had to adjust how I sleep a little bit b/c I'm a die hard stomach/side sleeper and found that the arm I was lying on tended to fall asleep b/c I wasn't moving around.
As far as being too hot - the sales rep told me that this was a problem with the earlier beds but that the newer beds the foam was a little different and it wasn't a problem. I noticed in the documentation that comes with the bed that it says that you may need one less blanket due to being a bit warmer. I have noticed that I do get a teensy bit toasty, but it's a little unfair to blame it all on the bed - between the pregnancy and sleeping in an upstairs bedroom in Sacramento I'm liable to wake up sweaty no matter what. I just switched out our duvet for a blanket and that helped significantly. I'm sure it's more me than the bed, since my hubby got cold with just the blanket and usually I'm the one who is freezing! :) I think you can splurge and get a fancier Tempurpedic if you want to and they have mattress covers that are more breathable which might help address the heat issue if you're concerned about it.
Also, Sleep Train has their 90 day guarantee (and I think they have some sort of special going on now where you can get free pillows or comforter or bed frame...not sure though!) where if you decide you don't like the bed you can exchange it for another bed and they will refund you the difference if the new bed costs less.
OOoh, one other thing - if you do buy the Tempurpedic, I would suggest also buying the mattress cover. For the queen it was only an additional $89 and it's given me a lot of peace of mind, considering that my water broke while I was sleeping when I went into labor with my first and when I'm in early lactation I leak a TON! There's a ten year warranty on the mattress cover and they'll replace the mattress if it fails due to a bad cover BUT you have to have bought them at the same time and have the receipt to prove it. So keep that in mind - that wasn't something we were told, I just am crazy about reading all documentation that comes with things so I noticed that.
So I would definitely look at the Cost Per Year for the Sleep Number bed and see how it compares to the Tempurpedic and factor that in before you make your decision. Hope you find the perfect bed!