Depending on what sorta fish you're keeping, there are different sands to consider.
African Cichlid sand: this stuff's expensive, and will raise the pH and hardness of your water. It's great for Rift Lake cichlids and would be neat for livebearers.
Black Tahitian Moon Sand: black sand. Looks great. Expensive.
Onyx sand: I think it's a little more coarse than other sands, but it looks good and is good for live plants. Also expensive.
Play sand: Great stuff! You can buy huge bags of it from Home Depot or Lowes for dirt cheap. (Actually cheaper than dirt cheap. I'm pretty sure it's cheaper than potting soil.) You have to rinse it a lot so it won't cloud up your tank, but it looks good.
The main drawback to sand is that you can't use any sort of undergravel filter, and you have to keep the sand stirred. The reason for this is that since it's so compact, sand can for anaerobic pockets - areas with no water flow where certain nasty bacteria thrive and can give off gases that are deadly to your fish. You have a couple options. You can stir the sand manually with a plastic fork or something on a regular basis. You can get Malaysian trumpet snails, which are a small conical shaped snail that reproduce like crazy (but won't harm your plants) and burrow through your sand looking for food. I've seen many, many fish stores that had huge infestations of these snails that were more than willing to give me some for free. This keeps your sand stirred for you. Many types of loach and corydoras catfish will also aid in keeping your sand stirred.
Now for the positives: sand looks great! Plus, those big bags of play sand make for a really inexpensive substrate. Also, the sand is much nicer to the sensitive barbels of certain bottom dwellers like cories and loaches, and works well with fish that like to burrow.
I tried play sand for the first time in my community 29g tank and fell in love. I've got sand in my cycling 29g as well. I talked my brother into using it in his new tank.