Not many can claim 25 years on the Internet! Join us in celebrating this milestone. Learn more about our history, and thank you for being a part of our community!
Being more into the programming side I really don't know much about the art side, apart for algorithims, effects, formats etc.
So I was watching a short documentary, 'The making of Unreal Tournament' and noticed some artist in the background using full sized tablets, is this just for concept art or can you actually create models, 2D or 3D with them, or maybe you can touch up meshes/wireframe models....?
I assume you mean these - http://www.wacom.com/en/us/creative/intuos-pro-l ?
In my experience, almost every artist will have one on their desks. For concept art / digital painting they're essential. For texturing they're almost essential. For 3D modelling, they're optional -- many just use the mouse, but I've also seen many artists who have gotten used to the tablet so much that they hardly use a mouse at all!
They're used for pretty much everything where there can be some form of strength or intensity variable. The pens are pressure sensitive, meaning you can "click" with different strengths. It can affect things like the size of a brush, the opacity or the sharpness depending on what tool you are using. I have an old Wacom tablet, but I find it kindof difficult to use since you can't see what you are drawing on it. Now that I have a Surface Pro which came with a Wacom pen, even I can somewhat effectively use those features.
Yes, tablets are almost mandatory for any 2D / 3D artist unless you do strictly pixel art. Tablet makes drawing with a mouse feel like a total waste of time.
For 3D they are used for texture painting (color and normal maps) or digital sculpting that is used to bake textures for lower resolution. Seamlessly control the opacity/size/strength of strokes and use the pen drawing motions you've practiced since you were a toddler...again there is no way you could get same results with a yanky and inflexible mouse.
For me it hasn't replaced mouse but I know some of those cases too. I find navigating 3D softwares too hard since pen+keyboard lacks scroll which I find essential in my work. Pressure sensitivity isn't needed outside painting/sculpting so mouse+keyboard offers more suitable forms of input. Also many use tablet+pen but I use too many keyboard shortcuts for tablet use.
A tablet can be used for anything a mouse can. To get used to their first tablet it is common for the artist to use it to play mouse-driven games with it. College professors use tablets as a virtual laser pointer or to add hand-written notes and arrow/circles/etc. to powerpoint projections.
Actually, if you have troubles using Pen+Mouse, try Pen+Space Navigator. That is the ultimative combination for 3D Work, especially sculpting, but the Space Navigator also works fine in Blender.
While all Space Mice are kinda expensive (3DConnexion has no competition at the moment, meaning they can charge whatever people are ready to pay... and the CAD Pros can pay a lot it seems), the basic versions are in the not-too-expensive category at around 125 bucks (at least here in Switzerland).
Takes some time to get used to, but then you will never ever use your Mouse again for navigation in 3D Space...
On topic, I am a humble Programmer myself, but do a lot of art too as I am developing on my own at the moment (and really like to draw and sculpt). I splashed out the lofty sum needed for a Wacom Cintiq 24HD at christmas 2 years ago and it was one of the best investments in my life.
If you draw a lot digitally, do 3D Sculpting or other stuff like that, there is no way around a Pen Tablet. And if you have just a little bit of artistic talent like me, but lack the time and expierience to learn to draw with a mouse or even just do the Hand-Eye coordnation stuff needed for a "normal" Tablet (like the intuos series), a pen tablet screen like the cintiq will really take your artistic work to the next level.
I know there are a lot of Mouse Users out there that will say something different. For me, its clear: I really like my cintiq!
I use my tablets for literally everything I do on the computer. Even in most traditional 3D applications (though I usually sculpt), there are ways to zoom around using a keystroke+a swipe of the pen or the pen button, so no scrolling is no issue for me, personally.
Mind you, I've been using a no-screen tablet daily for like 6+ years now, so there's also that. Anyway, they function as a mouse, too, so many artists use them for more than just drawing and painting. 
Our artist uses a Cintiq for all the sketching/painting/texturing stuff. It's all mouse for 3D work though.
I've seen a Wacom surface and the pen tilt used to adjust multiple aspects of music production with different elements linked as custom keys, like scrubbing through a timeline, pitch adjustments, BPS all the while leaving marks in the timeline to make notes for later. I've also watched video production use it the same way. Users can bind key strokes or macro key combinations to most tablets custom keys and since the Cintiq is a second monitor a user can have it as a entirely custom UI for most anything. It was pretty fun to play Supreme Commander with one back in the day since the game was designed with multi-monitors in mind. It's a pretty powerful tool since so many people are so accustom to using a pen and combined with multi-touch with Pen priority on a light weight OS a user can have portable tablets and even link those tablets with PCs for added functionality.
Now with so much writing recognition software algorithms could be written out (less fussing with keyboard symbols) using a tablet and convert the writing to text to add to software. I don't know if this is actually faster but I imagine it could be.
