Depends on the game. Some games have no DRM - aside from Steam, of course - even if the retail version has Securom or Tages or some other hellish scheme. Some have really simple DRM where you just copy/paste the cd key. Others, though, still force you to use another form of DRM. Bioshock 2 still uses GFWL, and Dragon Age Ultimate Edition still uses the built-in EA lockout that requires a key, EA registration, and a few other hoops. I don't think that Steam games use securom anymore, but I could be wrong.
If the game uses GFWL then the store page will have it listed in the "requirements" section. Your best bet to finding out, though, is to go to the Steam forum page for the game you are interested in and looking at the threads. Normally people bitch about the DRM if it's a problem, so if you don't see any large threads of that nature, you're probably good.
To be honest, though, if a game has awful DRM like GWFL or Ubisoft's stuff, I would say you should still buy the game instead of ignoring it, just wait until it's $5 on a sale.