Ya not a fan when non-Valve published games require Steam to play. The publishers chose Steam as their form of DRM rather then some other 3rd party like SecuROM, hence the need for it. While they are by far the least intrusive DRM, they are still DRM. As Mail said it'll be like any digital Steam game, you just have a retail box. However I'm not exactly sure what would happen down the road if say Steam shut down and you have a retail copy from a 3rd party publisher that forced Steam onto you and others. I'd hope they'd release a patch in order to play the game. They might not though and say you agreed to the terms when purchasing the game. I'm no lawyer but there seems to be a lot of gray involved with digital distribution which is scary.
I highly doubt something like this would happen in the near future though or ever so no worries. Just be aware that you don't actually have full rights with digital content or Steam DRM games. Like ability to resell, full control and the requirement for a 3rd party to play your games.