Most games that are not purchased on Steam can't be added to your Steam library, even if they're also sold via Steam. The most notable exception for this is games that require Steam in order to play.
Certain publishers have come to an agreement with Valve, and you can put your retail CD key into Steam in order to activate it. This knowledge base article contains all of the different types of keys that can be purchased separately, but then activated on Steam.
You've already noticed that you can add it as a "non-Steam game" with limited Steam functionality, which essentially boils down to:
- It will show up in your game library and can be launched from the Steam interface
- The Steam overlay will usually work, so you can open it, get friend notifications, use the browser, take screenshots, and do other things that the overlay supports.