The sting (heard in the beginning) played when the burglar (or other dangerous event, such as raccoons and getting fired) appears on your lot in the first game. The combination of the eerie music, the picture in picture highlighting the burglar's furtive and ever-forward movements, and the knowledge that you can't do anything about it - not even cheating! - save for calling the police, is simply terrifying. Oh, and sometimes it happens that a burglar appears in the lot, but leaves immediately afterwards...
Guide Dang It!: Place the burglar alarm on the outside of your house, or better yet, make a single-tile wall on the very edge of your property (even better, make as many walls as you have "entrances" onto your property), and place a burglar alarm by that wall if you want to make absolutely certain the police will have enough time to catch the crook before he steals your possessions. As a bonus, this won't wake your sleeping Sims, unless (for whatever reason) their bed is outside.
The Fire 2 sting is reminiscent of Psycho Strings, and it shows. You will no longer want to leave your Sims with the Cooking skill below 3, or put chimneys, or use the firework kits, lest you will hear that terrifying theme again... not that Fire 1 is not scary, but at least it lacks the strings.
Even if they're meant to be prank calls, a lot of the messages your Sims hear when answering a ringing phone are downright creepy. Special mention goes to "They're coming soon. Better think twice about opening the door." And while you read the message, the little mask in the dialogue box just stares at you. Smiling.
Here is a list of all the prank call messages; they run the gamut from mundane ("Wrong number. Sorry.") to innocuously bizarre and silly ("Do you canoe?") to vaguely unsettling ("The drop off has been made. You've been warned.") to FUCKING OMINOUS ("The end is near. Make preparations.").
The flashing light was just a test. You'll have plenty of warning next time.
The music when ghosts appear on the lot (heard in the same video as the sting above). The wailing and laughing noises they make when floating around doesn't exactly help.
Pretty much ALL of the danger themes in that video. Especially the death themes, combined with the message that appears. "Deepest sympathy! Sim has just died. Though body is gone, spirit will always remain". The fire themes are also very shocking.
From The Sims 2
It is unknown whether the alien babies come from intercourse or artificial insemination, but the only difference is the kind of trauma the Sim should have from that, especially after ending up with a green-skinned, black-eyed child that looks just like the aliens abusing you. Yes, the Mr Seahorse-scene is funny, but would you want to be the one who it happened to?
Worse, the fact that the ghosts can literally scare your Sims to death.
The Ottomas Twins. They've been known even to corrupt the game. There was a similar glitch in the original Sims, where a Sim's face icon would be a black-eyed, pointy-haired, big-lipped and generally deformed face, a bit like the Ottomas twins. When the player suddenly notices it, it's startling and frightening.
The hula zombie music. It IS a death theme, but it's still surprising how eerily spooky and depressing it is.
Any time an NPC brings their toddler to the library, the kid crawls over to the toychest and literally disappears into the box. And if you teleport home when holding the neighbour's infant (uh, kidnapping) there's a good chance the infant will turn into an invisible, unfindable, crying baby.
In World Adventures, at least two tombs have an inscription left by a previous explorer that ends with a variant of "I'm sure I'll find my way out any day now." Both have a skeleton lying a few feet away from them...
Nightmare Retardant since you can buy the skeletons from the buydebug cheat and the description says that it's a joke and made of plastic.
The same Expansion Pack also gives us the death by Mummy's Curse. A very unlikely death, given how it occurs, but it's pretty unsettling all the same. For starters, when your Sim loses the fight with the Mummy, they have a chance of casting some kind of spell on them, with them screaming all the while. And a few days in, a strange border begins appearing around the screen when the view is on the Sim in question, with a skull fading in and out of view, which gets more and more intense the longer the Curse is left. Eeesh. And then, when the time of death finally comes, the Sim is surrounded by a tornado and... just blows away. It's a very creepy death, especially in comparison to the rest of the deaths.
Seanbaby doesn't appear to be aware of this particular feature - when he does a brief Let's Play of the game as an experiment in "psychology", several of the gnomes he set up to stare at the toilet so his subject would never feel comfortable peeing relocate to surrounding guard positions during the solitary confinement portion of the experiment. When he notices this, he expresses doubts about his own sanity (the sanity of his subject isn't exactly in question - it's quite definitively not there).
The Werewolf transformation from the Supernatural expansion. The Sim looks (and sounds) like they're in pain when it happens. And if you're playing with the game on full moon, it happens whether you like it or not.
Sims with the Supernatural Skeptic has special social interactions. Some are funny enough, like "imply mother is a junebug" when talking to fairies, but some are... unsettling. For example: Look through the options and see "Enthuse about Witch Hunts." The speech bubble even depicts three torches. Keep in mind that witches usually were killed by fire.
Some of the new paintings for this expansion changes their apprearance at night... and all of their alternative looks are visions of hell, death or evil.
In the haunted house (The attraction, not a literal haunted house) look closely at the windows. This may sound stupid at first, but the person who dresses up as a ghost (By donning a bedsheet...) actually looks surprisingly creepy as it strides back and forth past window to window. It's the fluid movement that sells it.
If you forget to pay your bills for some time, a repo-sim will be sent to your house to reclaim some of your stuff (equivalent to the amount of money you owe). Unfortunately, the game gives no notification or warning whatsoever, and when it happens, a fairly ominous piece of music plays and suddenly a shady-looking sim in dark gray clothing enters your house and starts taking stuff. Many first-time players mistake the repo-sim for a burglar. Not exactly nightmarish, but still fairly alarming.
The Kraken that comes with "Island Paradise". Especially because it will often swim under your houseboat. Also, sharks.
The "glitched baby" problem, which can happen if you accidentally download corrupted custom content. Nothing quite like having your newborn baby look like a featureless flesh-colored stick or a giant blob which eventually grows up into a horrid, deformed, shapeless Lovecraftian mess.
Pets can have the glitched baby problem sometimes too (but no corrupted downloaded content is necessary). It's uncertain what causes it but while speeding up time in the PS3 Sims Pets sometimes dogs or cats will suddenly shoot up onto two legs with the distorted glitch form.
In-universe example. Checking for monsters under the bed. If you find nothing, fine. If you see glowing eyes. The kid will refuse to sleep in that bed for an entire night.
An in-game example. When Toddlers and Children play with an evil clown-in-a-box, they get scared to the point that it's recorded as a bad memory.
Mean (grouchy) Sims apparently do not get a bad memory when playing with the evil clown-in-a-box, but enjoy it instead.
Meteors. Overlapping with Paranoia Fuel , these things can fall and kill your Sim if you have Ambitions installed at literally any time you're outside. While there is a pretty big warning to get out of the way, and a meteor has to get a direct hit, it is more than possible to go AFK without pausing only to find your camera zoomed in on a smouldering, 3x2 hunk of rock when you return.
If the game is paused at just the right time, some of the Sims' faces can look downright spooky.◊
From The Sims 4
Children can't burn, but they can drown. You can end up with child ghosts.
You can romance the Grim Reaper, flirting with him whenever he comes to harvest souls. But he only calls when somebody dies, so to romance him, you have to kill Sims.
Vampire attacks at night. An ominous music sting will play as the vampire enters your Sim's house and does a Nosferatu-style walk to their bedroom, all with you powerless to do anything about it. If your Sim gets drained by Vlad, you may also get a message in which he declares his intentions to return and feed again whenever he wants, since your Sim's plasma is just that delicious.