They're pretty good if you're serious about cycling, though I always think it's better to just get out there on the road. The issue for me was that it was just a bit boring to pedal in front of the telly and that. I'd either rather be at a gym or out in the real world.
Don't get an air resistance one, whatever you do. Fluid or magnetic is what you want. Also keep in mind that you WILL need some sort of fan pointing at you while you're doing it, you'll get incredibly hot very quickly.
I used them for years when I was training seriously. They work very well if they're set up properly. They don't feel like real riding, but they're close enough. It's boring as fuck, but it's not too bad if you listen to podcasts or whatever. I made a little desk that clipped onto my handlebars so I could catch up on my reading.
If you use slick tyres, a magnetic or fluid trainer and a concrete floor, they're reasonably quiet. Treaded tyres, a fan-based trainer or a hollow wooden floor will make a right old racket. I preferred the units with continually-variable resistance like the CycleOps Magneto - the ones with a resistance adjustment lever are a bit fiddly for interval training.
You'll probably want to do your turbo sessions in an unheated garage or shed, otherwise you'll boil your arse off. You don't get the cooling effect of the wind on a turbo, so you get hot quickly. You might still want to set up a fan if you tend to run hot. If you sweat heavily you'll want to drape a towel over your frame, otherwise your sweat will rot your paintwork.