Special Person, Normal Name

"Bill? Vampire Bill?"

—The Sookie Stackhouse Mysteries

In real life, most people have very common and completely ordinary names. But not so in fiction. If a character is to have any relevance to the plot, he has to have a name that sounds either cool and tough, or ominous and evil. But sometimes they don't. Everyone else does, but not these characters. Their names are just so ordinary and common that it really sticks out.

This trope is the middle ground between Awesome McCoolname and Unfortunate Names. Compare with Aerith and Bob, when characters with completely made up names appear side by side with others that have completely mundane names. Might overlap with Mr. Smith. See also Fluffy the Terrible, Tom the Dark Lord, Sesquipedalian Smith, Some Call Me... Tim, and Odd Name Out.

Examples of Special Person, Normal Name include:

Anime and Manga

In Elfen Lied, the massive, floating head, Body Horror oracle that is kept in the pool under the research facility is just called "Anna". But to be fair, she was born as a mostly normal girl.
The Big Bad undead evil sorceress in Record of Lodoss War is just named "Karla", being about the only character in the entire world with an actual real world name.
The high priestess of the temple in Rune Soldier is named Jenny. Just "Chief Priestess Jenny".
As Magical Girl Lyrical Nanoha mages tend to have extravagant names, several fans understandably did a double-take when the main rival and possible love interest in ViVid, who also happened to be the female sort-of Reincarnation of a legendary king and hero, was revealed to have the name of... "Heidi". Cue short-lived Girl of the Alps jokes.
- Subverted, as everyone in-series calls her for her second name, Einhart.
Martian Successor Nadesico starts with a pilot named Jiro Yamada, the Japanese equivalent of John Smith. He's so upset his name isn't awesome he insists everyone call him Gai Daigoji.
"My name...is Roger Smith."

Film

Tim the Enchanter in Monty Python and the Holy Grail.
Bill.
Smith, Brown and Jones..
Inverted in Down Periscope. The name of the electrician is Nitro, but he's working on a nickname: Mike.
Played with in the poster for Men in Black (as the characters go by One-Letter Name, but the actors have Plain Surnames).
Mr. and Mrs. Smith.

Live Action Television

The all-knowing prophet "Chuck" In Supernatural.

Literature

Sookie Stackhouse starts giggling when her vampire love interest introduces himself as Bill.
The Ender sequels feature a one-of-a-kind life form, an artificial intelligence with a soul. The ansible network and all computers linked to it function as her nervous system. She thinks at thousands of times the rate of human thought. The name she gave herself: Jane.
James Bond is probably the ur-example. Ian Fleming choose it because it sounded so unexciting, although since then he's given the name an allure of its own.
Anne Shirley constantly laments having such a plain name. She wishes to be called "Cordelia", which she believes sound much more interesting. Though, if she must be called Anne, she's sure to remind you that it's spelled with an E, because without it, it looks even more plain and dreadful.
Harry Potter. In fact, Harry is one of the very few characters in his series to have a completely ordinary name. (Most of the characters with normal first names have weird last names.)
The Nightside's own John Taylor. Of course, in-universe, he's made it into a Name to Run Away From Really Fast.

Video Games

Subverted in The Curse Of Monkey Island. Guybrush asks one pirate his name, to which he is told "Bill". When Guybrush comments that this doesn't sound very piratey, Bill interjects with, "Cutthroat Bill".
Ed the Undying an ancient mummy in Kingdom of Loathing. Lampshaded in the encounter: "Ed? Not Ed-Ra, or Ed-hotep, or something?"
In The Lord of the Rings Online you frequently run into a strange old lady called Sara Oakheart, wo seems to know more than she is letting on. It's the perfect disguise, as her real name is Amarthiel, the Daughter of Doom, one of Saurons chief generals and a two thousand year old undead spirit, who had been spying on you the whole time.
While Solid Snake from Metal Gear Solid certainly has a cool-sounding code name; his real name is just "David".
- Big Boss' and Ocelot's real names are also rather plain (John and Adam).
  - Actually, Ocelot's real name is "Adamska". He says it at the end of Metal Gear Solid 3: Snake Eater. In fact, he asks Naked Snake what his name is and then gives his own. When Naked Snake answers simply, "John," Ocelot replies, "Plain name, but I won't forget it."
Pokémon Ruby and Sapphire gives us Maxie and Archie, the Magma and Aqua leaders. Unlike most characters, they have no theme nor pun to their names and seem to have been chosen because they start with the same letters as their teams and Japanese names, and sound a little bit alike.
Inverted in Thief with Viktoria, who has a suprisingly fancy and aristocratic name for a wood nymph.

Web Comics

The immortal "Jerry" from El Goonish Shive.
Biff in Absurd Notions names every one of his roleplaying characters "Biff".
Richard.

Western Animation

Phineas and Ferb, episode "The Chronicles of Meap":

I am known by many names. Well, two, really: Mitch, and some of the guys call me Big Mitch!"

Billy from Adventure Time. Who is a legendary hero.