i've seen straight, curved, and Cannula needles used for snug piercings. i don't think you HAVE to use any certain kind for such piercing, however the jewellery choice is limited and does often require a specific type. a curved needle may create a better hole for curved jewellery to follow and to heal with. snug piercings do often take quite a while to heal as well so keep that in mind.

If you use a curved needle get one that is short. Some pre-sterilize needles are really long in the curved. Now if you have the training to do it properly you can slightly bend the straight needle but you have to be really careful not to place a sharp bend in the needle not to pinch the needle closed not to slightly break the needle it needs to be a gradual bend I use my gloved fingers to do this slowly when I need a curved needle for a really tight spot because like I said most of the ones you can buy are about three to five inches unbent and can be a pain to deal with in small ears. 

I use them for the tragus.  I've used straight in the past, and I can make it work, but using the curved ones allows me to place the bevel of the needle behind and a cork in front, and I don't need to manipulate the tragus at all to pierce.  I've noticed that the client seems to experience less pain because I'm not moving the tissue.

I also like to use curves for this one.. It just seems to run alittle more smooth for me...