At the cost of being unable to naturally regenerate Magicka, the birthsign gives a large boost to your maximum Magicka and gives you a 50% chance to absorb incoming spells. Both games also include the Atronach birthsign.It is also far cheaper in terms of Magicka cost (or Enchantment cost if enchanted onto a weapon) than other spells which deal the same amount of direct damage. However, if the target has less total health than what the spell drains, it will be a One-Hit KO instead.
Its intended purpose is to lower a target's maximum HP temporarily while you use other attacks to finish them off before the spell ends, which to one with normal logic faculties sounds like a bum deal. Drain Health reduces your opponent's health, but unlike direct damage spells, the 'lost' HP is restored when the spell wears off.