I have been working on a MBP since March of last year and all I do is work with CS2. While everyone else here can give you partial experience and hypotheticals I will give you real answers.
I ran a Powerbook G4 before my MBP and had 1.25 gHz in it... My CS2 apps in my MBP with 1.5 gHz show no real drop off or improvement. When I had only the stock .5 mHz in the MBP it took forever to open the program, and heavy tasks did take a while. The change for you won't be noticable to what you are used to on your powerbook, and if you stick 2 gHz in it, you may even notice improvements, especially considering mine is a CD1 chip.
The true downfalls will come when you find you should only run 2 CS2 applications at a time... if you run more you will probably have one quit on you. I find InDesign to be the most stable, with Photoshop rarely quitting as well, and Illustrator quitting the most. That pretty much sums up CS2 anyway, Illustrator is always the one that could really benefit from an autosave such as InDesign has.
I don't have as much trouble running a lot of Rosetta apps, as long as the CS2 are kept down to 2 at a time. I regualarly have ID, Ill, Flash, Dreamweaver and Excel all open at once and while slow at times... it's not too bad considering how ridiculous that is. Office by far performs the worst under Rosetta... It's a monster, and anyone using Excel daily has far more to worry about than people using CS2.