Turmeric is one of the spices most strongly associated with Indian cooking - in fact, I have the impression that Indian restaurants outside of India tend to add it to almost everything partly to make sure that people know they are eating Indian food. It is added to curry in other parts of the world as well - in many places a curry just isn't a curry without it, although the Thais would disagree.
Turmeric has a mild, earthy flavour and an intense yellow colour, with the power to stain almost anything. Usually it comes off eventually if you scrub it with a mix of lemon juice and soap, and keep scrubbing for a really long time. The same component that imparts the colour, curcumin, is also chiefly responsible for the wide range of health benefits credited to the spice - it is a powerful antioxidant and anti-inflammatory agent, and there is fairly strong evidence that it can help to prevent cancer and mitigate the effects of Alzheimer's Disease. Its bio-availability is not high, but it is enough to show some definite affects from oral supplementation. Research is ongoing.
A rhizome from the same family as ginger, fresh turmeric is much the same shape, and has a similar warm spiciness to it along with a rich flavour with subtle floral notes to it. It makes a delicious infusion, rather like an earthier version of ginger tea; my best results involved blending it with white tea and some other ingredients, and I have recorded a recipe under that heading. Unfortunately, I have very rarely found fresh turmeric for sale in Britain - it is much more commonly dried and ground into a fine powder. In this form, it is best to make sure it is well-cooked, either by boiling or frying - the dried form of turmeric has a bit too much of that earthy flavour when it is raw, and is a little on the bitter side. It also loses almost all of its gingery character when dried. I had turmeric once that had been dried but not ground, which I found was rock hard and almost impossible to do anything with.
Besides its ubiquitous use in curries, turmeric also makes an excellent addition to many rice dishes, especially fried rice, and to pakoras, onion bhajis and other kinds of fritter.
Curcumin is a pH indicator - it turns pale yellow when you add acid, and deep orange or even red if you mix it with an alkali. This makes turmeric a more versatile dye than it would otherwise be. It also means that if you make pakoras with too much baking soda and no acid to set it off, their vivid red colour should tell you that you have completely ruined them.
Some information is from Gernot Katzer's Spice Pages, almost certainly the internet's best source of general information on spices. Here is a BBC report on research into turmeric's health benefits. Here is more from the Huffington Post.