It really depends on the software you're going to use. Like everyone is saying, some programs can take better advantage of the second CPU. IMO, a 2 CPU system isn't necessarily going to give you much more performance, at least not enough to justify the additional cost, considering that there are much better technologies available for a single CPU workstation.
I've also been thinking about building a 2 CPU system, just for the heck of it, though.