Most people now a days go with DDR3 because it's not very expencive and more boards support it as well as the later CPUs and GPU setups etc. Regardless of this though, I have (kinda slow) 800MHz DDR2 RAM in an otherwise high performance rig with a six-core at 4.0GHz and two GTX 260s, and have no issues playing all the latest games.
There is a big difference however when it comes to things like rendering or converting video. That type of process works so much faster with say 1333MHz or 1600MHz DDR3 than on my DDR2 setup.
An example..
I encrypted a 500GB hard drive. I had an X3 CPU at the time, with 8GB of 800MHz DDR2 RAM. The encryption took 9 hours to complete.
A friend of mine also had a X3 CPU at similar speeds, and he only had 4GB of RAM, but his was 1333MHz DDR3 RAM. He also wanted to encrypt a 500GB hard drive but it only too him 4 hours.
So really, if you're after games and performance computing I would suggest getting the DDR3 RAM, but if you don't mind falling behind in a few aspects such as loading times and rendering etc - DDR2 is still perfectly good as long as you have enough of it to go around.
Hope this helps.