1. The community is notoriously immature, in the sense that all the top mods will always be nvde mods, or mods that add russain anime girls to Skyrim. You'll never find an actual solid built, or non-lore breaking mod in the top endorsed/downloads list. As such, it can be hard to find the actual good mods - they are often buried under a mountain of crud.
2. You pretty much have to use their mod manager. It's nice, but it's annoying that you have to be a member (free) of their site to use it.
3. Mods that compete for popularity via oversized graphics on their profile, massive amount pleas for endorsments, etc are RAMPANT and there's a strong "modder ego" that goes with the Nexus that really isn't found anywhere else. In other words, many of the top modders on Nexus don't really follow the modding spirit, and instead just want to be super popular, even if it means their mod just adds a bunch of flashy, buggy bullcrap.
Workshop downsides:
1. Very limiting to modders. You can only have mods with 1 esp file (so no modular mods that have different options you can enable by selecting which ESP file you want to use), and everything you make is compressed in a BSA file format.. which is bad when there's only a limited number of BSA file formats that can be active at one time, last I checked.
2. No support for 3rd party script extenders, which means you can never get mods like SkyUI on Workshop
3. Search system is FAR worse on Workshop than Nexus. You can generally be pretty specific with what you are looking for on Nexus, while Workshop you can't. It's search feature is hard to find, and it does a terrible job. The only other thing to do is to manually go through giant lists of mods categorized by what they do.
4. You can't directly download mods, you can only "subscribe" to them. In that, they will auto-update whenever an update is released. This is very bad for the single reason that some mods that modify similar things, are often co-dependant on each other. So if one mod gets an update that another mod requires, you've broken the other mod until BOTH mods get updated. With the nexus, you can just hold off until both mods are updated. Sure you can unsubscribe right after you subscribe so you don't get updates, but then you'll never know when to update.
5. Load order (i.e. which mod loads first, which one loads last) is very important, because any mod that modifies something another mod modifies and is set to load last (including very small stuff you wouldn't expect, like adding a tree in a forest that a different house mod adds stuff to) will always have "priority". The default mod manager for Skyrim is really barebones in this, and the Nexus one (as much as I hate how bloated it is) is miles better than the default one. I'm also pretty sure whenever a mod is updated, it'll reset the load order. Which svcks if you have 50 mods in a specific order, then it gets reset on an update... you'll either have to remember what load order you had or go through trial and error trying to figure out crashes.
6. File size limits on mods through Workshop. This severely limits the type of content you can get from the Workshop.
7. Comment system is a bit wacky, unhelpful, and people tend to not care about the development of your mod. Despite Nexus's community ethos being awful, when it comes to small-time mods they are still generally helpful at "working with you" as you develop your mod, and are more able to deal with various bugs.
All in all, Nexus is better. While Nexus might have a pretty awful modding ethos and community, they are at least helpful and tolerant of "mod bugs" should they happen. And using Nexus ironically is much easier than using Workshop when you are actually browsing for and getting mods. The only thing with Nexus is you have to manually install them - while Workshop is automatic "click one button" style, since it is integrated with Steam. Because of manual installation, you can have any type of mod you could possibly make on Nexus, while the Workshop pretty much exclusively supports only simple mods like house mods, quest mods, etc.
Plenty of good stuff on both though.
