Get one, jailbreak it and install XBMC and nitoTV. Then install add-ons such as Free Cable, Icefilms (though recently Mega Uploads went down and the current content isn't as good right now), JustinTV, Veetle, ESPN, Hulu, etc.
This is no different than streaming content through your computer except it's more organized (lists of streaming sites organized by station, genre, etc...) it's much smaller and it hooks up to your big screen via HDMI. Also jailbreaking still allows you to retain all the original functionality.
If you are tech adverse...you can simply buy the ATV2 Flash Black for $30...will pretty much allow you to install XBMC and nitoTV without ssh'ing into the ATV and requires zero technical skill. To install the add-ons requires a bit more skill but with some research can be done pretty easily with a FTP software such as Cyberduck and Filezilla.
Personally if you're already use to streaming and don't have cable it's great but I find the annoyance of streaming sites going down too much for me.
I would recommend buying one from Best Buy or Futureshop (they have 30 day return policy as opposed to 14 at Apple), jailbreaking it and installing all the add-ons and then using for a couple of weeks. If you don't like it, restore it back to the factory settings via iTunes and simply return it.
Edited by Jrufraz - 1/22/12 at 5:08pm