If you want sheer curtains, choose a fabric that is either a lightly woven nylon or polyester. The idea of using a sheer silk may seem luxurious, but the fabric quickly fades and deteriorates in sunlight. Synthetic sheers last much longer. You can also use a lightweight cotton and polyester blend, which has a nice drape and lets filtered light in.
If you want to sew a valance or a set of cafe curtains, a mid-weight woven cotton, such as a quilting weight cotton, will serve you well. When choosing the fabric, unroll it from the bolt a bit and hold it up diagonally. The fabric should fall in gentle folds. If it does not, put the bolt back, and look for a different fabric.
Velvet, linen and heavier cotton/polyester blends make excellent long curtain panels or drapes. Simply because the fabric has a heavier weight does not mean it should have more body than lightweight curtain fabrics. Make sure the heavier fabric drapes well before choosing it to use as curtains.
Flannel does not make a good set of curtains on its own but is perfect to use if you want thermal curtains. Sew the flannel in between the exterior curtain fabric and the lining fabric to create a layer of insulation to keep your home warm in the winter.