Night Terrors to me is a distinctly average (not really mediocre, just average) episode. That is, the premise and atmosphere is interesting enough -people slowly losing their minds over a lack of REM sleep- but some of the execution so silly and needlessly drawn out and dragging on, e.g. with that recurring Troi 'nightmare' where both parties couldn't get more creative in their communication attempts than 'where are you?' and 'Eyes in the dark. One moon circles.' even after repeated attempts.
But the one 'scare' scene that really does it for me is Beverley in the morgue, with the bodies suddenly sitting up, Beverley taking a few steps back in shock, bumping into one that's at her back, her concentrating, telling herself it's not real, looking again and seeing the bodies lying down again. Still sends a chill through my spine after all those years. So simple, yet so effective.