Depending on who you talk to, you're going to hear a number of answers to this question. The short answer is yes, tattoos hurt, to some extent. The experience of tattoo pain varies widely, depending on placement of the tattoo, its size, the skill of the tattooer, and the pain tolerance of the client. Many tattoos are more irritating than painful, especially during the healing stage, when they tend to get very itchy. You certainly shouldn't let fear of pain dissuade you from getting a tattoo, although you may want to think about placement if pain is a big concern for you.
The size of a tattoo will have a major influence on how much it hurts. Extremely large tattoos hurt as your skin gets irritated and raw, which is one of the reasons why a tattoo artist may arrange multiple sessions for a large piece. A small to medium sized tattoo may not be terribly painful, because endorphins quickly kick in and carry the client through the tattoo.
Placement is also very important, and tattoos on the bone or near nerves are going to hurt. This includes ankle tattoos, tattoos along the spine, and work around the collarbone and nape of the neck. Fleshier areas of the body tend to be less painful because the tattoo needle is not striking a bone. Knowing that tattoos can cause pain in sensitive areas, you might want to ask an artist to do a dry run with the needle in the area first, to see how painful it really is.
Some companies sell numbing cream for tattoos, a practice that has met with mixed response from the tattoo community. Some artists think that since tattoos hurt, it is not unreasonable to numb the skin beforehand. Others are concerned that these compounds may compromise the integrity of the inks used, or they may increase the risk of infection. More importantly, numbing creams take a while to work and they wear off quickly, so they may not be as effective as the body's natural tools for fighting pain.
It is important to remember that everyone's response to pain is slightly different. You can decrease the potential of a painful tattoo by talking to your tattoo artist about your concerns and eating a healthy meal beforehand to settle your stomach. Stay hydrated as well, as this will promote rapid healing of the site. You should never be ashamed to ask for a break in the tattooing, and you should alert your artist if you feel faint or uncomfortable. Tattoo artists are well aware that tattoos cause pain and that different people handle the discomfort differently, and they will not criticize you for asking for a break.
As you are getting a tattoo, it may help to breathe deeply and think about the meaning behind the tattoo to distract yourself from any pain. It may also help to know that many tattooed individuals say that they don't remember the pain of a tattoo after they've received it; in fact, many people feel great after the completion of a tattoo, even if they experienced brief discomfort while it was applied.