Sins of a Solar Empire - RTS that's a bit slow to get going (depending on game settings) but rewards you with some epic battles
Star Lancer - I personally really enjoyed this. Simple yet engaging storyline using a tried and tested formulae (think Wing Commander) executed exceptionally well with enough interesting additions to keep it fresh, well, for it's time anyway. Not sure if there are any texture packs or mods for this though, may have to play it “as is” but it’s still playable if a little dated. The way capital ships break up after being torpedoed is one of many highlights

- Has excellent intro / cutscenes!
Another vote for Freespace 2... excellent story told very well. Use the link posted by HeX, all you need is there... play the first and then the second, no excuse really... looks absolutely gorgeous despite it's age. Besides, it's the playability right!?
I loved I-War but imagine it would be a labour of love and difficult to get into for some, especially now. I used to play it all the time when it was first released and for many years after. Haven’t touched it in years now though. Worth a try! Again, excellent story and as someone already stated, the true (or near enough) Newtonian physics engine makes for a really unique experience… combat is both exciting and as in depth as you want it to be… for example, leave everything on auto and you can fly around, shoot things and complete missions. But if you want to get hands-on and take control, feel like the captain of a vessel, you can take things to a whole new level using the various console screens… gunnery for advanced targeting and engineering for manual damage control.
I-War 2 is a game I loved to play but never had the time to finish. Takes I-War to a whole new level from a graphical perspective and overhauls the interface / control system to make it less intrusive and more user-friendly. To be honest, it took me a while to get used to it and I missed the various elements that made the original I-War the perfect game for me but it certainly wasn’t a bad sequel.
As for the X series of games, never had time for them. Someone will do doubt correct me if I’m wrong but my understanding is that they are very demanding of ones time and can be difficult to get into due to the sheer scope of things. So if you have the time to invest in X3 / Terran Conflict (which I believe is said to be more first time user-friendly) then go for it! In a nutshell… gorgeous, deep and time consuming!
All in my humble opinion of course