I had one for awhile. It is a really painful piercing, and it'll make your eyes water horribly. It's over quick though. I have a small nose too, and it looked good. I had to take it out though because I was allergic to the white gold stud. I kept it in for a few months, but it just wouldn't heal because I have severe metal allergies. So when you get it done, make sure to get a stud or ring that is made of something like steel or titanium, and not a mixed metal like white gold.
When I got it, I knew I was allergic to nickel that they sometimes put in white gold, so I got white gold with palladium. The end result was that I was slightly allergic to the actual gold which never allowed my nose to heal. Anyway, as long as you don't have the problems I had, I think you'll enjoy a nose piercing. I got a really small stud, and it looked nice.