The sting (heard in the beginning) played when the burglar (or other dangerous event, such as raccoons) 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.
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.
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 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?
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.