Silent Hill 3 is the best horror game I have played, much better than Resident Evil 2 IMO. But to specify what makes them different:
Resident Evil 2 has a lot of zombies wobbling around the place, and finding ammo is hard, so you can get a "desperate" feling when you try to run away from them. The triggered events are mostly *crash* *mwwaar* *zombies appear* style. Cheap scares and lots of action, quite hard and illogical puzzles (put a medal into a statue and it drops a key?) I didn't notice much random/ambient scariness, although the corridor with crunching glass shards on the floor was not nice to walk through... A good game, but nothing amazing, says me. And the graphics in RE2 might seem a bit old.
Silent Hill 3 has a slower pace, and there's a heavy feeling of something sinister lurking all around. The video sequences are very frightening in the way the most ordinary, everyday things can turn to scary... Like the model dolls in a clothes shop, when they all have their arms extnded towards Heather... Well, Silent Hill 3 has a deeper plot than the RE's, and I like that very much. The puzzles are very logical, although sometimes weirdish. What do you do with a hairdryer in a sewer? Well you plug it into a wall socket and throw it into the water to electrocute a monster! Also, the puzzle and action difficulty can be adjusted apart from each other (I use Action level Easy and Puzzle level Normal for my first game, and the action is a bit too easy). And there's one thing that I really admire: the ambient sound effects. When you walk through an empty corridor, you suddenly hear a low wail. Was that a door that creaked? Was that a monster? Really, a lot of sounds have at least two meanings, neither of which is calming... Oh, and since nearly every other person in the game is a loony of some sort, and the "dream world" places are freaky to the extent of hurting your eyes, you/Heather really get the feeling that you are alone. Altogether a damn scary and good game. Haven't finished, yet, though. By what I know you can understand the plot better if you've played SH1, but it doesn't matter that much. Oh, and the graphics are excellent.