I used to have a water bed. It was terrible for sleeping on as it would always suirm this way and that whenever I moved. It was very hard to get comfy and once you do fall asleep and you make a sudden turn it can transform your pleasant dreams to bad ones. Sex was good though, but I still prefer a regular bed for that too.
Tempurpedic mattresses are also very nice, but only for sleep.