I have a 3x13 maple and 3x13 steel. I get several comments on how good the 3x13 maple sounds. I personally like my 6x14 maple better, but since I got so many compliments on the piccolo I started using it more - I finally got a chance to hear a live recoring of a gig, and it did sound good.
I think funk/R&B, reggae/ska and certain pop works best for piccolo, and rock lends itself more to a deeper snare (although I see more and more rock setups with a piccolo).
Although I can understand Tim's dislike for piccolo (his posts have indicated that he prefers very big drums) I don't know if I completely agree with trying to tighten a bigger snare to get a "piccolo" sound.
Almost all drummers like the snare to be pretty tight, but I try to avoid getting any drum so tight that you choke the life out of it. I find you can actually tune a good piccolo down (a little) and get a fairly fat sound (and still get a crisp attack - without choking the drum).