It is maintained by a group that keeps a list of ad sites (and porn, and spy-ware, and so on).
You'll observe two main ways your web experience changes. One is that you won't be able to visit any 'questionable' site. The other is that ads on web pages won't populate.
A side benefit is that pages will load faster since content (usually advert images) doesn't have to load when you visit a page.
As for google, when you click the sponsored links (the one's that have a colored background at the top of the results), the link 'passes through' an ad site that counts your hit as coming via google. Well, these ad counting sites are commonly found in the hosts file, so the link behaves like a broken link, since, by design, your computer thinks you host the ad site. You don't, and the page displays with a 'Not Found' message.
The other cool part is that, if you have neglectful surfers (like kids or a wife) who click on links without thinking, the dangerous sites are often simply not found when surfing on the computer you maintain a hosts file on.
Excellent advice.
As GreenAccord states, websites will appear cleaner/less cluttered, and load faster. (I.E. People on dial-up won't have to wait several minutes for those annoying shockwave/flash ads to load.)
For those interested, the MVPS HOSTS file is free, and works with all versions of Windows. It is updated about every two weeks.
Just download the ZIP, and extract the HOSTS file to the appropriate directory:
Windows XP = C:\WINDOWS\SYSTEM32\DRIVERS\ETC
Windows 2K = C:\WINNT\SYSTEM32\DRIVERS\ETC
Win 95/98/ME = C:\WINDOWS