Empire asked 15 visual effects artists to list the greatest effects shots in film. The list is intrinsically interesting because it was selected by people who are actually working in effects today, but the way the artists describe their reactions to these scenes. It's worth checking out the list (and watching the attached clips).
For me, there are two that really stand out. The dogfight above the first Death Star in Star Wars looked so real to 9-year-old me that I believed in an entire universe as a result. I saw Jurassic Park on opening night when I was 18, and was expecting Jaws on land. The brachiosaurus reveal told a completely different story, 10 minutes into the film.
What is your favorite visual effects shot?