@theartfuldodger – it depends on a lot of things as to what wood you use. Here in Texas we tend to use cedar because it is a renewable wood and somewhat resistant to insects and weather. Some of my Dad’s fences in California were redwood, from before we realized what the logging industry was doing. Redwood, like cedar, lasts a long time. Other fences were pressure-treated pine, a renewable wood but the chemicals are not the best for the environment.
If you are going to paint, stain or otherwise seal the fence, then solid “pre-owned” wood can work just fine. Also, if you are going to design it such that the edges will be secure from moisture, then plywood works well. You can also combine wood with items such as fiberglass panels, for privacy and a windbreak, but still allowing light in.
If you really want to have fun, you can design a fence with all sorts of recycled building parts in it, even windows. I saw one fence in Austin, Texas, that had old windows incorporated into the section along the street – it gave some privacy, served as a wind break and kept the dogs from roaming, but still let the neighbors enjoy the abundance of flowers in the yard. You can add in arbors, benches, and all sorts of other special designs if you want.
Here are a few varieties of fence and this site offers ideas for using cedar. This site shows some neat custom fences you could copy.
Have fun with your project. If done well you can really increase the appeal of your house and yard.