I didn't love what I've played of Bioshock (I do plan to finish it soon though) but the opening is incredible. The crash (the fire on the water is so awesome), the elevator ride, everything is fantastic.
Yeah Bioshock is easily like 2nd on my favorite games list (as least out of the more recent years) and it's a great game to me, really, but there was a LOT packed into the intro. No real filler either, it was just a small scene then BOOM you're dropped into the game.
The ride down into Rapture built up so much atmosphere and the first encounter with the Spider Splicer was cool. Plus, it's like "Hey I haven't seen that splicer in a while" until you finally start fighting Spider Splicers way later in the game; so it's cool to recall the beginning when you finally face one.