Rear Curtain Sync can be useful when a slow shutter speed and significant continuous ambient light (room light or sunlight or light bulbs, etc) can be expected to be blurred due to the motion, and you want to see that blur trail (or at least to place it following the motion instead of leading it). The very fast flash duration will stop the motion, however a slow shutter speed can let ambient blur it anyway (if there is significant continuous ambient light to do it).
Front Curtain Sync is the normal flash mode, with the flash being triggered near the start of the shutter duration The flash finishes quick, and freezes the motion, and then the slow shutter remains open longer, and can blur due to the continuous ambient light, So the ambient blur appears later (out in front of where the flash fired), appearing to lead the motion. Not a natural look (when there is blur from the ambient).
A distraction: The D90 and D7000 (and many Nikon models with Auto mode) are now instead naming Front Curtain Sync mode to be called Fill Flash mode in the menu. No actual change, it is still Front Curtain sync (everything but Rear Curtain Sync is). Puzzling, this menu does not actually cause Fill Flash, TTL BL flash mode does that (and the internal flash is TTL BL). It is just another name now, meaning "regular flash sync". Specifically, either name (Front Curtain or Fill Flash) in this menu is just a default menu choice which specifically means: "None of the Above". It simply does Not select Rear Curtain.
Rear Curtain Sync waits to fire the flash until almost the end of the shutter duration, so that the ambient shutter blur occurs first, which then appears to follow the subject (appears back where it previously used to be, before the flash), which looks more natural to the motion. Only point of Rear curtain sync is to be used with a too-slow shutter speed, to intentionally make the blur trail visible. Rear Curtain Sync simply will not matter at any normal fast shutter speed (if no blur trail).
TTL flash fires twice of course, preflash for metering, followed by final flash when shutter opens. These are normally too close together to perceive two, but rear curtain flash delayed to the end of a longer shutter duration will clearly separate them. Two flashes is normal for TTL.
The three pictures below are a tape dispenser swinging on a string (the motion is to your right in all cases). The ambient room light is morning window light, but not direct sun. D300 settings were ISO 200, 50mm, 1/20 second f/4.5, with a hot shoe manual flash at four feet. Frankly, 1/20 second seems abysmal to stop motion, but this is indoors, and I am trying to show blur here, blur caused by the ambient light. The SB-800 flash is at 1/32 power, which has a flash duration of 1/17800 second (spec chart in rear of flash manual). The flash certainly stops the motion of the swinging tape dispenser fine (note its shadow), but the 1/20 second shutter still allows the continuous ambient room window light to blur it severely anyway. There are clearly two separate exposures in a flash picture. The flash is very fast, but the much slower shutter continues the continuous ambient exposure much longer after the flash has finished.
Front curtain sync, 1/20 second shutter - Motion to the right.
Flash flashes first, then remainder of shutter time exposes the ambient (ambient leads flash)
Rear curtain sync, 1/20 second shutter - Motion to the right.
Shutter time exposes the ambient, then flash fires as the shutter closes (flash leads ambient)
Said again: The shutter duration is represented by the longer blur trail of the motion. The instant the flash occurred is represented by the sharper location of the subject at that instant. The point is, Rear Curtain Sync fires the flash last, as the shutter closes, which causes flash to appear to lead the motion, which is more natural.
Some people imagine that the delayed result of rear curtain sync causes a sharp stopped image superimposed on top of the blurred image (so is sharper), which may appear true of the leading edge, but the opposite is true of the trailing edge. So while there definitely are two separate exposures, and the flash does freeze the action when it triggers (and the continuous light continues blurring it), the effect is not "on top" of anything. Each pixel can only contain the one total accumulated pixel exposure value, regardless of when. The only difference is when the flash occurs. Rear Curtain sync only determines if a blur trail follows the subject instead of leading the subject. There is really no point of using Rear Curtain sync except with the slow shutter speeds that can cause blur trails from brighter ambient.
Rear Curtain sync will also ignore the 1/60 second minimum shutter speed menu limit with flash (in camera A or P modes), and will use the SLOW shutter speed the ambient actually meters. Rear Curtain Sync mode is intended for SLOW shutter speeds, it only has use for SLOW shutter speeds which can produce the blur that Rear Curtain addresses.
Same thing said another way:
Be that as it may, note that the point of the Part 4 page is that if we had wanted to eliminate the blurring, we would eliminate the continuous ambient light by using maximum shutter sync speed (around 1/200 second) instead of a very slow 1/20 second. That fast shutter keeps out the continuous light, which is generally easy indoors, but it is more difficult outdoors in sunlight. Sure the shutter is set to be 1/10 of the duration, which is faster, but not really enough for the action this close. But also the continuous ambient light is now weakened to be over three stops more dim. Its blur is simply too dark to be seen. The shutter speed does not affect the flash (because the flash is faster than the shutter speed), so the flash exposure was unchanged, but the ambient exposure was decimated.
Front curtain sync, 1/200 second shutter (motion and flash same as before).
Shutting out the ambient prevents any blur from shutter speed.
Saying it again, as it is very important, and slightly tricky. The 10x faster shutter should make a blur trail that is 1/10 as long. The tape dispenser is 3.2 inches long, and the blur trail above seems about that same length too. So for a 10x faster shutter speed, we would expect the blur trail to be reduced to about 0.3 inches long then (still as wide as a pencil), which would still be a blurred picture in this close view. Which would be obviously true if we also opened the aperture the same three stops to create an "equivalent exposure" of the ambient. But we didn't open the aperture! The flash saw the same aperture and still provided the same exposure (not affected by shutter speed). But the ambient became over three stops more dim, due to faster shutter speed. Light that is too dim to be visible adds no visible blur. The picture is sharper and the background shadow is darker, NOT because the 1/200 second shutter was able to freeze anything, but only because the 1/200 second shutter kept the continuous ambient light off of it (no addition of two sources) - letting the speedlight do its fast work.
Repeating song and dance for third picture: It is NOT that the 1/200 second helped enough to freeze the motion of the swinging string. The speedlight flash (1/17800 second) was more than fast enough for that. The benefit is that the 1/200 second shutter speed kept the continuous ambient light out, too dim to blur the action. This was easy indoors, in more dim ambient light. It is harder to ignore full sun, but sports action flash pictures do often intentionally darken the ambient background a couple of stops, for this same reason - to minimize blurring due to ambient (and to make the subject stand out against the background).
The third picture is all the same, the same flash, same room, same motion, just without any contribution from the continuous ambient light, due to the faster 1/200 second shutter speed shutting it out. It seems very important to realize this distinction - it is not that the 1/200 second is fast and stopped the motion (1/200 would still be insufficient speed to do that when up this close). The flash stopped the motion - 1/32 power is 1/17800 second duration. What happened was that the ambient was made to be very dark and invisible at 1/200 second exposure, when it simply didn't matter any more. No visible blurring because it was dark. This is a biggie to realize and know, very usable.
And it is easy to do indoors with dim ambient, but much harder in bright sun, because the maximum shutter sync speed is limited, and the flash power has limited range. Cameras vary a little, but 1/200 second is the ballpark of as fast as the focal plane shutter can open to sync flash.
Auto FP flash (HSS): Note that so-called High Speed Sync (FP mode) cannot help do this, since the HSS flash itself becomes continuous light then too, same as the sun. The faster shutter can help the sunlight exposure, but the FP flash cannot stop motion, and the FP flash distance range is reduced. Rear Curtain Sync has no meaning for FP mode, and no possible benefit (FP flash begins before shutter opens, and remains on until after shutter closes). Continuous light has no motion stopping ability anyway. FP shutter speed is faster than 1/200, but still nothing like the speedlight already does. A fast shutter speed will attenuate both HSS flash and sun the same (both are continuous). etc, etc. Auto FP flash mode is about using wide aperture with flash in sunlight, and is not about the flash freezing motion (a fast shutter speed can help in bright ambient, if the FP flash is bright enough then).