I tend to agree with Perone on this. The D800E is not a camera to be afraid of in normal photography. I have one on order and cancelled an order for first day delivery of the D800.
Moire is a potential issue with fabrics and extremely regular man-made structures. I wrestled with potential moire on bird feathers, and concluded that the large number of feathers at different angles and limited size of any one feather would minimize moire. I see moire as being a potential issue for product photography, architecture, and possibly with portraits depending on clothing.
Diffraction is a different issue. Diffraction neutralizes moire. Diffraction at high apertures is likely to cause some softness - and it will vary by lens. But the sharpest portion of your image will have more detail and be even sharper on the D800 than any earlier camera. The D800E inches up a bit more on sharpness and detail beyond the D800.
Here is an example of depth of field at f/8 using a 20mm focal length and a subject 5 feet away. Near limit 2.63 ft Far limit 51.2 ft
If you increase aperture to f/11, here is the impact: Near limit 2.2 ft Far limit Infinity
In this case with a wide lens, it really makes little difference. Your closest in focus area changes by 5 inches, and you pick up a little sharpness over 50 feet away. And if you have been shooting at f/13 or higher, there was minimal benefit.
You probably won't expect huge depth of field with longer lenses as longer lenses and close subjects have small depth of field at any reasonable aperture.
The real benefit is from sharpness in your subject. If your subject is truly sharp, it may make out of focus areas appear softer. We already do that in editing (sharpen the subject and soften the background). This capability will mean you need a little more skill and planning to optimize your photography.
Finally - the D7000 has essentially the same size pixels and image size as a DX crop on the D800. The D7000 produces very nice crisp images - but it can be demanding on technique. I've used it for everything form events to birds in flight and have gotten plenty of very sharp images.