To answer the question as stated "How does Fog Of War work in StarCraft 2?" it works by preventing you from seeing changes to stationary structures (i.e. buildings, destructible rocks) in places that you do not have vision or sight. It also prevents you from seeing units that are out of sight entirely.
To answer the question you probably actually meant, which is how does sight work, each unit has a specific sight value. For example, the Mutalisk has sight of 11 even though it only has a range of 3. A Siege Tank also has a sight of 11, but a range of 7 in tank mode and 13 in siege mode. A probe on the other hand only has sight of 8.
Non-flying units also cannot see up a cliff, so terrain plays a factor. There's also certain terrain (smoke things on Metalopolis, some tall grass) that prevents seeing past them, so they can be used to hide units for an ambush.
Air units' sight is unaffected by terrain, so they always have their full size. Additionally air units can be seen by ground units even if they're flying over a cliff, so they cannot abuse the high-ground to snipe ground units below (but they can use the high ground to quickly run away)
Additionally Colossi are unique in that they behave like a ground unit as far as what they can see, but they behave like an air unit as far as being seen (i.e. if they are on top of a cliff, they can still be shot at from below, assuming they're in range).
I think that covers everything, but I might be missing a thing or two.
In Starcraft it was known as "Hunters," but in Broodwars it was called "Big Game Hunters" or BGH.
It does exist as a custom map
Best Answer
Fog of war cheats ("maphacks") definitely exist.
How to detect when you've been maphacked: If you suspect that you have been maphacked, load the replay. Watch the game from the perspective of your opponent. If he appears to be spending lots (abnormal amounts) of time hovering his screen over your base in the fog of war, chances are he is hacking, as in the following video.
This technique has been used to expose hackers quite a lot in the BETA phases. If you are able to detect the presence of a maphacker using this technique you can use the "report user" feature within battle.net and post an explanation in your report.