Fate/Stay night tells about the happenings of a secret war, set in Fuyuki City and involving 7 magicians, called Masters and the heroic spirits they're able to summon and make a pact with, or well the Servants. This conflict implies the victory of only one Master who's able to realize one true wish through the Holy Grail. The war is going to start pretty soon, involving many changes in the life of Emiya Shirou, the main protagonist of the story. Fate/Stay night is divided into 3 indipendent scenarios yet linked to each other: Fate (the one which is also featured in the anime), Unlinited Blade Works and Heavens Feel (which is probably the most significant).
Since I don't want to give too many spoilers all togheter I've made short descriiptions (basically the main events in general) for each part of the game.
If you don't want to have major spoilers don't read them!

fate: introduction of FSN, also the anime serie is majorly taken from this scenario.
unlimited blade works: few events but more space to characters developments.
heavens feel: ideal conclusion and meaning of the Grail War.