TV tuner cards have come a long way in the years since the previous writeups, mainly due to the arrival the Freeview terrestrial digital TV system.
TV tuner cards in the past were fairly complex cards, as they had to tune into the broadcast signal, decode the TV picture and then digitise it into a format for the computer to display. Hence they were usually PCI cards to get enough bandwidth, and enough physical space on the board for the electronics.
Now, things are different. As the Freeview signal is already an MPEG-2 signal, the card doesn't have to do any digitising itself. It still has to tune into the appropriate "channel" (more properly called a multiplex) and select the actual "channel" from within the multiplex (Freeview multiplexes typically contain about 6 TV channels). Once it has de-coded this channel - really no more than an MPEG-2 data stream - it then feeds it into the computer for displaying in the normal way.
This means that the TV tuner cards are much simpler devices. For example, you can now purchase a Hauppauge twin Freeview tuner on a USB stick that's only slightly bigger than a normal USB memory key, for about GBP 50. This has two tuners, so when running with the appropriate software, you can be watching one programme while recording something else altogether.
Furthermore, the software has improved as well. In the past, each card came with a dedicated piece of software to drive it - and some of these left a lot to be desired. However, most of the newer cards are fully supported by Windows Media Center which provides a superior interface to the channels on the card, full interaction with the Electronic Program Guide, DVR capabilities etc.
In short, the tuner cards of the past were best used as a "second tv", or to allow you to watch TV in a window while working. The modern cards, however, especially when coupled with Windows Media Center installed on a small and quiet PC, can take the place of a traditional TV tuner altogether.
Sources: my purchase of a Hauppauge Freeview tuner stick and the research done around it.
ascorbic suggested I mention that Freeview is the UK brand for DVB-T - free-to-air terrestrial digial television.
rootbeer277 asks "can you use this to use you monitor to play home console video games? And if so, is there a display delay to worry about?". I don't know of any console that outputs Freeview-type signals. All the older ones output composite (or other analog signals), and the newer ones (such as the Playstation 3) output HDMI. So I don't think the situation is ever likely to happen.