If you're not concerned about breaking the game, the best enchantment on a weapon, by far, is Dominate Creature for 1 second, Dominate Humanoid for 1 second and Soul Trap for 1 second. Set the level for the dominates to at least your current level plus a few (higher if you want it to be useful longer, or you can always make another one). It's completely and totally unstoppable-- every time you hit something, it'll not only do damage, but make whatever it is NOT attack you. You won't even fight any more-- you just slaughter things while they stand there and take it. And it's about the least fun possible in the game.
So-- that out of the way-- I think that Drain Health 100 is probably the most powerful useful enchantment in the game, though it's arguably even a gamebreaker. It works best at lower levels, when you're facing things with fewer than 100 total HP, so it's a one-shot kill, but it remains useful throughout the game. Later, when you're facing things with more than 100 HP (or 200, if you add a 100% Weakness to Magic with sufficient duration for the weapon) it still provides a hefty advantage, since it means you only have to get opponents down to 100 (or 200) HP in order to kill them with the next strike.
Personally, I rarely use it, just because it's a bit broken. I don't like making the game that easy.
My favorite enchantment, by a considerable margin, is simply all three types of elemental damage plus soul trap. With a grand soul gem, it's easy enough to do 15 points of each of the damages plus a 1 second soul trap, and that's more than enough to handle pretty much anything I'm ever going to face. No matter what it is and what sort of resistance/immunity it has, there's going to be something that's going to damage it, and often something that's going to do extra damage. Against a Frost Atronach, for instance, the frost part of the enchantment isn't going to do anything, but the fire part is going to do extra damage, so it all evens out. By adjusting the order in which the effects are listed and the amounts of them, it's possible to get any of the three animations/shaders, so I just tailor the enchantment for the look I want-- be it fire or frost or shock-- but the weapon always does all three. And with soul trap there to make it essentially self-recharging (along with Azura's Star on a hotkey), it's brutally efficient.
I've experimented a fair amount with weakness effects on weapons, but all in all I just don't care for them that much. If you just use weakness to the elements, you really don't gain much if anything. Weakness effects are considerably more costly than damage effects, so you have to decrease the amount of damage the weapon does in order to provide enchantment points for the weakness effect, and it works out almost even as it is. Add to that the fact that you then have to get a second strike in before the weakness effect runs out in order to get the full potential damage, and I think it's just more practical to stick with pure damage. Adding a weakness to magic effect can add quite a bit of damage (as Zaria already explained well), but you still have the time constraint imposed by the duration of the weakness effect, and if you don't get off another hit within that time, you're all the way back to scratch and starting over with a weapon that, on its own, does less damage than a similar weapon with just the damage enchantment would do. I just find the benefit of the weakness to not be enough to make up for the time constraint. I'd rather be able to back off and parry my opponent if that's the best strategy without having to deal with the necessity of continuing to strike just to keep the weakness running.
And for a bow, nothing beats drain speed 100 plus a bit of elemental damage plus soul trap. Or, as it's often known around these parts, the Acadian bow.