I see this question asked a lot, and I've touched on the subject previously a bit. I figured I'd pass along all that which I've learned over the years from fellow stitchers and my own experience. I'm going to discuss tapestry needles because I have the most experience with these and use them most often.
There are loads of different brands of needles out there. What you use is personal choice and trial and error is the only way you'll know which is best for you. Surprisingly, it's not something a lot of stitchers think about often - they'll often use whatever is close at hand or the ones that come in kits and keep using them until they get lost! The thought of doing that makes me shudder, btw. I'm very particular about my needles now. They are VERY important to my stitching pleasure. A bad needle will be noticed immediately!! I really didn't know what all the fuss was about until I got fed up with eyes breaking and decided to try some other brands.
There are certain things you want to look for in a needle.
First, find one that works well with your body chemistry. I'm acidic apparently, I start to tarnish a lot of brands within an hour or two of using them, even though I wash my hands constantly. I've personally found that Piecemakers and Bohins work much better for me in this regard, they still tarnish but not nearly as quickly as others. I think I've been using my current one for about a week and it's still shiny. I go through them a lot more in summer mind you, sometimes changing needles every day. For some people, gold or platinum plated needles will hold up better. Why is that so important? Well using a tarnished needle will remove that nice smooth feel as you pass it through the fabric and even get to the point of feeling "sticky" for lack of a better term. If it gets badly worn it can also leave tiny metal deposits in your work which may not show now, but on an heirloom piece you want to pass along, they could definitely show up later on and discolour the fabric and threads around them. When your needle starts to tarnish, it's time for a new one.
Next, look for needles that are well made and have a smooth eye. Some brands I've tried have burrs in the eyes which can do serious damage to your floss. How do you know? Your floss should glide through the eye smoothly. If you feel it snagging a lot it's probably catching somewhere inside the needle's eye. This is really frustrating, especially when you've spent a fortune on really nice materials and they're fraying all the time! Test out different brands until you find one that you like in this regard. Again, I've found Piecemakers and Bohins serve me very well and while I do find the occasional needle that snags, it's very rare.
The only thing I wish for from Bohins and Piecemakers is that they made petite needles - I'm a pretty frugal stitcher. I spend a lot of money on flosses and it pains me to have to throw away those little pieces. The shorter the needle, the less you're throwing away. However, I have tried every petite needle out there and have never been as happy with the result so I'll stick with the ones I love.
Now on to sizes. NOT ALL NEEDLES ARE CREATED EQUALLY. There are different sizes of tapestry needles for a reason! Using the wrong sized needle can create a multitude of problems - threads fraying really badly, threads tangling and snarling frequently and frequent knotting are the main ones. What I find frustrating is that more often than not, the stitcher is likely to blame the thread for these problems when often it's not the thread at all but often because someone is using a needle that is too small. Heck, I've even done that, blamed a particular thread for fraying a lot - yet when trying a different needle the problem disappeared. So now I try different sizes BEFORE I start cursing a thread.
A proper sized needle should do several things. It should be large enough to open up the holes in the fabric so that the thread can pass through without being abraded by the fabric. It should also have an eye large enough to accommodate the thickness of the floss you are using without causing even more wear on the thread. There are lots of different sites out there with recommendations of what size to use for what job, but I don't really follow those guides myself. Especially since most of them are solely based on the count of fabric you're using and not on what type of floss you're using. Here's what I was always taught and how I choose the size of needle I want to use.
First, it has to be easy to thread. If the floss I'm using is fairly thick, a #28 just isn't going to do it. Trying to cram a perle or even some of the thicker silks like Silk Mill or a metallic braid into that tiny little eye can be extremely frustrating. If it's a chore to thread the needle, and you're finding that the thread separates a lot or bunches up when you're trying to get it in there, it's probably a good idea to go down a size to a needle with a larger eye.
Second, the floss should glide through the eye while you're stitching. If it's getting snarled up or dragging a lot or even snagging on the end of the eye all the time, it's possible that the eye is too small to accomodate the amount or size of thread you're using.
Last, it should be large enough to open the fabric holes up and smooth their edges. And because fabric weaves can vary from brand to brand, just because two fabrics have the same count doesn't mean you should use the same sized needle for both! For example, 32ct Lugana tends to have smaller holes than Belfast linen, which has smaller holes than Permin 32 ct linen. You'll know you're using the right sized needle when your floss glides through the fabric smoothly - if the floss is dragging really badly, try a larger needle.
It's truly amazing how many problems a different needle will cure. I used to avoid a lot of threads because they frayed too much, or knotted too much, or tangled a lot. I also swore by my #28 needle for everything. It was on the advice of a fellow stitcher that I decided to try a different one and I was absolutely amazed at the difference. Of course some threads still fray, knot and tangle no matter what you try, but it will at least lessen the effect to some degree.
I'm not going to suggest different sizes for different counts - there are a multitude of sites on the net that will give you that information, and they're a good starting point to help you decide what to try first.
With all that in mind, if you always stitch on the same count, with the same threads and the same number of strands, none of this will really concern you. If you're like me and use all different threads, fabrics and counts, then you'll probably want to keeps a wide array of needles in your stitching arsenal!