I ask because there's been a perceptible trend in TV towards rehabilitating villains, recycling them after a mindwipe, or making them partly sympathetic.
The formulaic long-term villain
Q on Star Trek: The Next Generation was one example of a villain who is following this "villain arc" of "nemesis to mentor". Benjamin Linus on Lost is another. This evolution involves some kind of fall that causes the villain some humbling loss, whether this derives from a lack of conscience, literal intrinsic or supernatural power, or the loyalty of henchmen. The loss takes them within the circle of the good guys - sometimes resulting in defections, sometimes with a relapse and double-cross. On occasion, this defection is part of the main story arc: Athena from Battlestar Galactica (BSG) and Ming's daughter Aurra on Flash Gordon fit this description. Sometimes, in TV series that belong to the "Fugitive genre", you have the "hunter" character being ousted and eventually turned to good. Alien visitor series such as Starman and The Visitor exemplify this. You can also have the opposite effect, where former allies turn coat (24 is infamous for this).
Occasionally you have situations where the villains and good guys join forces against a common adversary out of necessity. Sylar of Heroes is such an example, as are several of BSG's Cylon models (especially the Sixes and Eights) and several of the Wraith from Stargate: Atlantis.
Sometimes they aren't pure villains, of course. They could be unwitting or naive traitors who didn't realize the possible consequences of their betrayal, such as BSG's Gaius Baltar.
The antivillain: redemption, sacrifice, and rehabilitation
Generally, the weak-willed villains don't generate as much sympathy. A really interesting villain, like an interesting tragic antihero, needs a hook: strength of character, a past shared with one of the heroes, or some heroic potential. The best villains, IMO, are those that hold the potential for redemption yet are always hovering on the edge of darkness. There are a few television shows, such as Xena: Warrior Princess and Angel, for which this is the original premise.
Of course, to make the show watchable, there must have already been some repentance of change of motive on the villain's part - or so conventional wisdom says. For one thing, it's hard to cheer for someone still going around killing and oppressing people. For another, the show must go one, and if cost of redemption is sacrifice, the villain's transformation often carries with it the seeds of his death. Whether immediate or eventual, the past has to catch up with him - and that means that most redemption becomes part of the hero's background. Think of all the conversions that happened in a prequel, or pilot, or somewhere in the distant past. Besides Angel (and Spike, insofar as his character in the final seasons of Buffy and Angel was heroic), there are several Highlander mentors such as Darius and Methos that fit this description.
The Death Dealer: Epic Prowess Harnessed
Another great quality of the villain that is sometimes more of a plot element than one of character development, is his prowess and his ability to inflict great harm on his enemies. This, too, is something we love in antiheroes such as or Buffy's Willow. (It's also a characteristic of so many antiheroes from film and literature, from Homer's Achilles to Underworld's Selene.)
We watch the assassin deal death, enthralled by his epic prowess, but we stay to see him unleash his wrath on the common enemy, or have his powers harnessed thus. Why do we love the "divide and conquer" plot? Perhaps it's just the feeling of satisfaction at such providence. There was a crossover episode of Stargate: SG-1 and Stargate Atlantis ("The Pegasus Project") where the heroes cleverly used the technology and force of two archenemies (Ori and Wraith) to destroy one another. In other media, the Alien vs. Predator series of movies, games, books, and toys are highly popular, its products hotly anticipated among fans.
In conclusion, I think the ultimate villain needs to have some redeemable qualities, but we also enjoy watching him be a "badass", literally bad. We need the two in equal measure, and we revel in the tension, the conflict, and its cathartic release. There's a good reason why villains such as Darth Vader resonate with us, why antivillains such as Celia Friedman's Gerald Tarrant and Julian May's Marc Remillard generate more interest than goody-two-shoes heroes, or even consummate bad guys such as Mercedes Lackey's recurring villain, Kiyamvir Ma'ar aka Leareth aka Mornelithe.
So, who is my own favorite villain? Stay tuned...