I'm a purist so I'm only going to focus on the mods that don't deviate from the spirit of the game. Other mods are more radical and creative.
Some of these changes are meant only to increase quality and not to alter the design. Others include fixing all the coding bugs, upgraded sound, better looking weapons and a host of other changes by generous contributors. That's one of the aspects of the game that's been vastly improved. If you read my top 15 PC game review, you may have noted that I criticized the crude graphics. But things have come a long way since then in the modding community.
If you have played the best game of all time, developed in 1994, you're ready to fast forward five years and play the 1999 sequel. So you're finally ready to play System Shock 2? Great choice! Good for you! Have you played System Shock 1 yet? No? Hmm.