SCS is great; it rewrites the AI routines of most of the enemies so they're not completely stupid and easy to exploit. Suddenly, enemy thieves are drinking potions of invisibility and backstabbing your squishiest party members (your mages WILL remember to have Stoneskin up at all times, or else), and enemy mages are taught to use spells like a diabolical sorcerer with an INT score of 18 actually would, plus their spell-books are randomized, so they're suddenly casting more than just True Sight and Chaos over and over. Mages also insta-cast a bunch of pre-battle buffs at the start of combat, so you're not the only one walking into a battle buffed to the gills anymore, and you can't rely on interrupting their spellcasting with ranged weapons to make them die like peasants anymore, either. Remember all those horrible creatures your party faced that were actually so easy to exploit that they didn't feel like threats? SCS makes them scary again.
I won't lie: sometimes, the fights can be frustrating. When Kangaxx instantly casts Protection from magic Weapons + Shadow Door + Spell Immunity: Divination at the start of the fight, you're going to be cursing at your monitor unless you've come equipped with plenty of Protection from Undead scrolls and a thief with points in Detect Illusion. When I first met the uber-ogre mage, Kahrk, in BG1 with a 6th level party, I was completely stymied: I just couldn't beat him with any non-cheesy tactic I tried. But the level of difficulty makes the game much more satisfying and epic. You feel like you've earned your victories. Beyond the AI revamp, SCS adds a bunch of "Tactical Challenges": completely re-egineered battles that make some of the climactic fights in the game more interesting. I haven't found one of these yet that wasn't a huge improvement over its vanilla counterpart (The Wolf of Ulcaster remains one of the coolest fights I've had so far).
Another mod I'd recommend is Spell Revisions. A large percentage of the spells in the original game were simply useless. Infravision, seriously? Even very high-level spells like Wail of the Banshee were just crap. SR revamps these to make them worth taking a second look at. This mod does make a few changes that I don't like, for instance, Sunfire no longer ignores magic resistance, Pierce Shield no longer lowers MR (in vanilla, it's the only spell that can break Demogorgon's MR), et cetera. You can easily customize the mod files to prevent it from installing changes you don't want, however.
Super epic BG theme for you if you actually read all that blathering: link