Network activation will activate a plugin for every site in a network whereas regular activation will only activate a plugin for the site you’re currently on. As far as implementation goes, there is one other important difference:
If your plugin is built to do something when it’s activated (via register_activation_hook()), this will fire automatically when you activate a plug-in normally, but it will not fire for a network-activated plugin until you visit the admin screen for each blog.
So if major database updates are tied in to register_activation_hook() they won’t occur until you log in to the other sites.