I have both editions and almost exclusively use the EE now, but as the others said, changes in single player aren't that significant. The main reason I got it (on sale) is for the enhanced edition of Darkness Over Daggerford (and later Tyrants of the Moonsea), both of which I had already played in their previous free versions for Diamond, and yet I thought these enhanced editions were really worth it. They are expanded, use new tilesets, voiceovers, music, even feature some new and exclusive creature models in the case of Tyrants. And I wanted to support the re-release of the previous premium modules Pirates of the Sword Coast, Wyvern Crown of Cormyr and Infinite Dungeons, which aren't officially available for Diamond anymore (only as "Abandonware").
I'm quite invested in the NWN community and like playing new community modules - which are few and far between these days but more likely to be EE exclusive now. The better multiplayer support is definitely a plus, too, and there are some improvements with regard to the use of custom content (which is now stored in the Documents folder, separately from the installation folder, just like in NWN2; also, using patch haks to manage override files works better now, and there are other advantages that you only notice when you're into this sort of thing, like, you can now have up to 255 override heads per race during character creation already, which wasn't possible in Diamond).
The downsides are that the EE introduced a few new bugs/performance issues (of which I've only experienced two so far myself - a serious drop in performance due to the sparkle effect on pixie and faerie dragon familiars, which can be fixed with a community override; and some door transitions behaving oddly occasionally), and some older community modules have new "bugs" because of it, too (e.g. interactive placeables embedded in walls slightly shifted their position and are no longer reachable in some cases, or very rarely, some scripts don't seem to trigger anymore.)
If you don't play NWN a lot, and you're mostly just interested in single player, be it the official campaigns or old legacy modules, and you don't care a lot about modding the game, then Diamond would be absolutely sufficient. If you're an NWN enthusiast, it can't hurt to have both editions, just lower your expectations a bit. There are a lot of changes and improvements, but most of them are "under the hood" and not really relevant and visible to the average (single) player, sadly.
Tallima: Here's a fantastic document that lays out all of the changes.
Interesting, I didn't know about this yet. Quite handy to have, thanks!