Steam is free. Completely.
The only time you pay is for games themselves. You purchase the game as you would with an digital distribution service and download it. Steam is essentially a launchpad for the games, it's needed in order to run them, but you can download the games onto multiple computers as long as Steam is installed on them (But can't play at the same time, since it's on the one account)
It also provides up to date patches. Though you can disable the automatic updating too.
In addition, Steam also has some of the best sales and offers (Example: Preorder Portal 2 and get Portal free). Especially around the holidays.
You can also remove the game from your system (Say for a reformat or just to free up space) and redownload at a later date for no extra charge. You only pay once for each game (Except for monthy fee MMOs obviously)
It's an excellant service for purchasing games, launching them and keeping them organised (Though if you have dial up it's a pain when you're waiting several hours on a download)
Edit: How does it work? It's witchcraft (Best said in a Brummie (Birmingham) accent)
Seriously though, once you have it set up and the game installed/downloaded, it's a pretty smooth operation. Launch the game via the shortcut icon and the game will pretty much launch as normal.
Generally a constant connection is best, but it can be set to offline mod if you can't have 'always on' Note: Going offline does stop the auto-updating and you won't be able to play some games if you go offline and they are midupdate.