The D800 is interesting because with it, Nikon took a huge leap in pixel count. At 36 MP, the D800 offers 1.5x the resolution of the D3x (its highest resolution predecessor with 24 MP, from 2008); at the time of release, it has the highest resolution of any full-frame DSLR on the market. Many are quite enthusiastic about this megapixel boost. It lets one crop almost indiscriminately in post-processing. It also allows poster-size prints, as it essentially packs near-medium-format resolution in a 35mm body.
However, some folks believe that 36 MP might be a bit of an overkill--after all, not everyone will make a poster print of all that many photos (if any); lugging around, transferring, saving, and editing all these extra bits may be unnecessary and inefficient most of the time. And of course, packing more pixels in the same sensor format necessarily increases noise, so some might prefer fewer, but larger sensors with even better, more stunning high ISO performance. The decrease in pixel pitch (relative to the D4 or the D700) also makes hand-held shots more susceptible to camera shake. Some skeptics would argue that with anything but the very best lenses, one would not be able to leverage the high pixel count; if your lens is what's limiting sharpness in the system, dividing the sensor into more pixels merely resolves a blur into a finer blur--it doesn't help. The same argument applies to shooting at small apertures where the image becomes diffraction-limited.
That said, these concerns should not be any worse on the D800 than on current DX bodies. The D800 has a pixel pitch of about 5 microns. That's indeed much smaller than that of the D4 (7.3 microns) or the D700 (8.5 microns), but is about on par with those in the D3100 (5.0 microns), D7000 (4.8 microns), or the D5100 (4.8 microns), for example. Therefore, while the D800 would be more sensitive to camera shake, small apertures, and lens flaws, at 100% zoom, you should be able to achieve the same sharpness as on a modern DX body. (And when shrunk and downsampled to fit on the same screen or the same print, you would get a sharper image, improved by the 1.5x larger "original.") However, this also means that as you crop more and more aggressively, you may eventually do no better than shooting with a DX.
Needless to say, all else being equal, more pixels can do no harm (other than hogging up storage and CPU cycles); and indeed, the D800 trumps its antecedent (the D700) in both low-light performance and dynamic range (see DxOMark). Therefore, if you don't plan to crop, the D800 should give impeccable images, especially if downsampled.
Versus the D4
The D4 and D800 are often compared because they were announced within a month of each other. However, the two bodies really target different markets, as evidenced by the fact that the D4 costs twice as much. They now become Nikon's two flagship FX (full-frame) DSLRs, and Nikon really tried hard to differentiate the two this time. (The company's two previous flagships--the D3 and the D700--are too similar, and the D700 eventually cannibalized the D3.) The D800 pushes resolution, while the D4 pushes low-light performance and speed.
Compared to the D4, the D800 is especially palatable to wedding and portrait photographers who use professional lighting in a studio. Its record-breaking resolution allows poster-size prints that are useful for clients in those markets. On the other hand, sports and wildlife photographers would certainly appreciate the D4's lightning-fast 11fps continuous mode, while rock concert editorialists would no doubt lust after its extreme 204800 maximum ISO.
Versus the D700
As a replacement for the D700, the D800 should be a very satisfying upgrade. Even with much smaller pixels, the D800 has better low-light performance and higher dynamic range (as tested by DxO Labs). Among other upgrades are a slightly larger screen (3.2" vs. 3.0"), a viewfinder with better coverage (100% vs. 95%), a slightly lighter and smaller form factor, dual CF and SD card slots, and of course, 1080p video capability to boot. The only downgrade is speed; the D800 clicks at 4 frames a second in full-frame continuous mode, one fewer than its antecessor; having to crank out 3 times as many bits though, this ain't all that bad.
The D800E is a sibling of the D800. It comes without the moiré filter*, and costs $300 more. (*This is not an irony, and it's not Nikon's new ripoff scheme; it's just a gross misleading statement on my part. The D800E still has multiple filters on top of the sensor, but these are cleverly arranged, and their birefringence cleverly reversed, to eliminate effective blurring due to those filters.) On digital cameras, moiré filters are installed above the sensor to prevent interference patterns due to subsampling of the image. It is essentially a low-pass filter that blurs the image slightly to suppress its high-frequency components, eliminating moiré. Since the D800 has such a high-resolution sensor, aliasing due to subsampling should be less of a concern, but some unpleasant artifacts may still show up, particularly with clothing, or anything with very fine grid patterns. Unless you really don't want the anti-aliasing filter, it is probably safer to go with the standard D800, since moiré patterns are almost impossible to remove if you do get them. (It's also difficult to see them on the camera, since the LCD screen itself introduces moiré.)
However, the D800E can in fact produce sharper images, so if you know how to identify and combat moiré (shooting at different angles, varying the focal length, for example), it might work for you. Try finding the D800E at:
Investing in good glass is always worthwhile, but with the D800, it's even more so. If you dish out $3000 for a D800, don't skimp out on glass. By far the most exciting feature of the D800--its no. 1 killer app--is the whopping 36 million pixels packed in a standard 35mm format sensor. However, being able to take advantage of all these pixels presumes that different pixels can see different details of a scene; in other words, everything else in the system--from the object being photographed, to the photographer holding the camera, to the lens--must come together to enable two adjacent pixels on the sensor to resolve two different points of detail.
Again, if the lens is what's limiting sharpness, having a world-record-breaking-resolution sensor won't help; a blurry blob divided into a million pixels is still just one blurry blob. Using a lens with a resolving power that matches that of the sensor is therefore necessary to leverage the real strength of the D800.