"Carnival of Souls" always was frightening to me, maybe because it's so quiet and dream-like. Surreal films tend to frighten me for some reason. I couldn't go to sleep after watching "Eraserhead!"
When I was a kid, my church made us watch "A Thief in the Night" and it was terrifying - even scarring - an end-times scare-tactic movie (didn't work on me, but only served to give me nightmares for no good reason). Years later I talked to a guy who thought it would be a good idea to make the church youth group he ran watch it . . . it had the opposite effect as they LOVED it and wanted to watch it at every event and they all started dressing up in 70s outfits. He almost cried telling me about it - and I can't stop laughing when I think about how hurt he was.
Really, though, for me the scariest movie starts and ends with The Exorcist - the perfect horror flick because you kind of forget you're watching a movie. The nurse with the giant scissor tool in the Exorcist 3 may be the single most terrifying moment in a film - I remember people leaving the theater. Otherwise The Shining, The Ring and Blair Witch are the only ones that even remotely got to me.
I'm losing it!! I should know my horror, but I just CAN'T remember at this moment which major horror movie has the priest who gets stabbed by the pole that comes off the church steeple. THAT really scared me for a long time!! I can remember watching that in the drive-in with a bunch of my girlfriends (yes that dates me LOL). They were all laughing at the movie and I was sooooooooo scared. The movie was the first in a series and I've actually seen all of them, but my brain just will not compute right now! Off to work!
David Lynch's Lost Highway—the man has a direct link to his subconscious, and our latent issues must be very similar. Really, Lynch gets me often: I still get the feeling, sometimes, that I'll look down and Bob will be grinning up at me from the foot of my bed!