In the wind/marching/concert band world, trumpets and soprano clarinets are always in Bb. The orchestra, however, uses trumpets in Bb, C, and D, and clarinets in Bb and A. I understand that clarinets in A would be preferred for pieces written in sharp keys, and trumpets in D are rarely used outside of solo environments because of their harsh sound, but what about the other incarnations of these instruments? A trumpet teacher at my university refused to play anything but a C trumpet, but how common are these instruments in the real world? Would one expect a small-town community orchestra, for example, to have A clarinets and C trumpets at their disposal, or are the Bb instruments the only reliable option?
Both are extremely common in orchestral music, and any professional musician (or even a serious college student or hobbyist) who plays classical music should have them. And even if they don't, they should be adept at transposing.
I wouldn't classify the higher pitched trumpets as "harsher", but rather "brighter". The C trumpet is noticeably brighter than the Bb, so composers choose one or the other based on the desired sound quality. On the other hand, Bb and A clarinets sound mostly the same, so it's a matter of technical ease (clarinets have pretty nasty keywork and are more affected by awkward keys than other woodwinds, aside from bassoon).
I can only comment the clarinet part of the question: clarinets in A are common and even amateurs have A and Bb instruments (the Eb clarinet is a different story). It has nothing to do with the size of the orchestra. The part may state clarinet in A and as player of a transposing instrument this leaves you with the choices:
- use the required instrument
- transpose mentally in real-time
- create a transposed version of your part to play from
Typically a concert is exciting enough, so I would not risk option 2 on that occasion except having lot of experience with it; for rehearsal it may be sufficient.
Yes, both are common, and are the predominant instruments in modern American orchestras. For clarinets, the most commonly used are A, Bb, and Eb; for trumpets, C, Bb, D/Eb, and A/Bb (piccolo). (In other parts of the world, mainly Great Britain and Russia, the Bb trumpet is most widely used.) In the US, by far, the A clarinets and C trumpets are most common.
The higher pitched trumpets aren't "harsher" if played properly, but just have a different sound – brighter and more brilliant. 95% of the time, the C is preferred, mainly because it cuts through the orchestra better than a Bb, and most of the standard repertoire is slightly easier to transpose from a C-pitched instrument. For chamber music, especially solo works, the C is very common. The D/Eb, usually a convertible bell setup, is used most often in chamber and solo music, especially the Haydn and Hummel concerti, but is used in orchestra when an even brighter sound is desired, or when transposition is easier from an Eb. Some works specifically call for an Eb. The piccolo is most often used in Baroque works, or when a really bright, penetrating (yes, maybe harsher) sound is called for.
To your question about which trumpet to use for community orchestra, the Bb is more than fine. Orchestral trumpet parts are written in many different keys, so some parts will be a little more difficult to transpose from a Bb, but there is nothing that you can't play on a Bb that you can on a C. (In fact, for a few works like Carmen, you need a Bb to play the really low F.) Remember that Bb used to be the predominant trumpet until more recently, and many European orchestras still prefer them as their primary instrument. Another thing to keep in mind is that the C is a very different instrument to play than the Bb, and playing a C well requires lots of time on it. Intonation is especially more difficult on a C. At the amateur level, you will be better served practicing the Bb than splitting your time with the C.
It should also be noted that until about 30-50 years ago, the predominant trumpet was the Bb. Today, only British and Russian orchestras still use them as their predominant instrument. Also, most central European orchestras today (mainly German/Austrian) use either C or Bb rotary trumpets as their primary instruments. In addition to the C, Bb, D/Eb, and A/Bb, professional American trumpeters usually have (or have access to) at least a half dozen more, including rotary, cornet, flugelhorn, and herald.