This game is getting a little long in the tooth now, but it still has the ability to surprise and shock. I recommend turning up the resolution of the game as high as your monitor/ graphics card can stand, to get the best from the game. The early levels are particularly scary; set in an old creaky 19th century mansion, abandoned church, delelict monastery, overgrown gardens, etc, and really push the original 'Unreal' engine which it uses, to the limit.
Monsters don't come at you thick-and-fast like they do in many fps's, but very often jump out at you when you least expect it, after a prolonged period of calm, or on the outside levels they may circle your position for a while at the extreme of your vision, trying to outflank you, before turning and charging. This tends to increase the tension, as it gives you time to think about what may happen next, between assaults. Unfortunately this approach is not maintained throughout the game, and later on, when you enter the 'other dimension' things become much more traditional.
The story itself is not much to speak of, being the usual 'gothic horror demon' plot, but the puzzles and set-pieces really help to move the story along and to provide the feeling that your influence is important in stopping the horrific events that unfold around you, (even if your control is really pretty limited in a game sense).
On the whole, worth playing if you fancy an fps that's a bit different, (particularly if you can't afford the latest uber-PC) or you like horror stories.