Steam restricts some Oblivion mods (that require OBSE), but works with all of the Mount and Blade mods and Fallout 3 mods I've tried. The issue with steam is that the retail .exe files are encrypted with their DRM so mods that depend on changing it don't work. The vast majority of mods that involve just adding files to directory structures work just fine. As for patches, steam generally has the latest patch for each title but you do want to let steam install them automatically because again there could be a DRM issue if you did it yourself. This varies based on the title so if you have concerns, google your target game along with "steam patch" or "steam mod".
Indeed. Not every Steam game behaves in the same way. Always best to check out the games you are considering.
Company of Heroes, for instance, can be run without Steam, by running the .exe file in the install directory, meaning that you can do what you want to it as you would a boxed/disc based version. Plus it updates itself through its own stuff.
Steam is only used if it feels a need to validate media (such as when its own key based authentication isn't working).
Obviously that's only one example.
Some games have robust mod support, like Red Orchestra or pretty much all Valve/Source based games, since it's all pretty much built around Steam, but others you are going to be less fortunate with.
Facebook
Twitter