Preamble: Here is my complete video review on Amazon.com that is now the most helpful critical review, thanks to your votes:-)
It is an amazing camera by Nikon, especially for amateur and enthusiast level photographers. The feature list is fantastic, plus the fact that it has the sensor of similar quality as the professional grade D300 model, and of course how can you forget the HD video capabilities? The sensor is highly rated for amazing dynamic range and color depth (the blues and greens are dreamingly rich, see my images in the customer images section). Please watch the video for a comparison with Rebel XSi that I owned before this one.
 Great sensor, amazing colors out of the camera
 Auto D-lighting to restore shadowed regions
 Feels great in hand, very comfortable to hold... lighter than a Canon 40d/50d.
 The optical viewfinder is bright and big, with great coverage
 AF tracking points are easy to spot and switch to
 Great 320K dots LCD
 Video feature is great.. you can make some really slick short movies after some practice. It is a bit difficult to master both autofocus and zoom and exposure control in video mode, but I guarantee you a huge share of fun with it.. especially with DOF tests:-)
 My copy of the camera came with one big bright hot pixel that was ON even at low ISOs like 200 and at fast shutter speeds. I was just about to learn to ignore it by using the software to map it out, and 2 more developed within a week of normal usage. Not good for a $1K camera right out of the box. I searched online and found many many others with the same issue. Further, you will have to pay at least one way shipping to a Nikon service center to have them mapped out, and yet they say it may develop more with time. I dont understand why a brand new camera has this issue. And its not jut my copy, you can search "Nikon D90 + hot pixels" and you will find many many recent posts on this topic.
 The live view is just a gimmick. It doesnt stand in front of the implementation in a cheaper Canon XSI even. It lacks exposure simulation (the mode which brightens or darkens the LCD view based upon live shot setting changes like ISO/aperture/exposure time etc.). Further, it only goes as high as 6.7X while the XSi and 40D/50D go upto 10X zoom. To add to that, D90 seems to extrapolate the live view image at high zooms from a lower resolution image... showing bad interpolation artifacts like blocks at 6.7X zoom. You have to see it to believe it. Try a Rebel XSi and a D90 live view, and you will know what I mean. Also, there is no exposure scale overlay on the live view image to guide the user whether the shot is under/over exposed. You will have to switch to optical view finder to confirm the shot exposure level. All this renders the live view practically useless for anything but framing a snapshot or a video. In fact it should have been called an LCD view finder and not the live view, which it is not. D90 also only has one auto focus mode in live view which is contrast based, and too slow. Even 40d has a fast phase detect mode. Also, the D90 LCD refreshes only 15 frames per sec while a 40d/50d refreshes at 30 fps - better for sports. If you wear glasses and are hoping this live view will help you take pictures without having to goto the top LCD or the optical view finder, you will be shocked. Having owned an XSi and tried a Canon 50d, I can say with confidence that Nikon really has to improve their live view, or at least stop calling it that. I have let the Nikon customer support know of the interpolation issue at least, and they say it may come as a model update later, probably not an easy firmware update. But their answer was not clear. It took many emails just to explain the problem.
 The zoom (+/-) buttons are for the left hand. They may have been like this for most of the Nikons, but coming from Canon, it was a big discomfort to me to first switch the camera weight to the right hand, then move my left hand to use the buttons to the left of the LCD, then switch the hands back. This can be a pain if you have a heavy lens (like 70-300mm VR) and don't have a tripod.
 The pixel depth is only 12-bit as compared to 145-bit in a Rebel XSi or a Canon 40d. This is not that obvious, and it is actually surprising that D90 produces great colors for a shorter pixel depth.
 The video mode can possibly age your sensor quicker than it should. Because of prolonged exposures (upto 5 minutes in HD mode), the sensor may develop more hot pixels over time than usual. It is also hard to lock exposure while in video.. else there are jerks when the auto-exposure-compensation mode jumps in as the scene brightness changes.
- if you can live with a sensor that may come with hot pixels, or may develop more sooner than expected,
- if you don't mind using both hands to manage controls while also holding a heavy lens,
- if you don't use live view at all and will only use the optical view finder, and
- if you have about $1200 to spare (as of Jan 12 2009), and a few hundred more down the line for expensive Nikkor zooms,
then this is a great camera for you. You will be more than satisfied with the colors that come flying out of it. Otherwise, allow me to recommend Canon 40d or 50d with pretty much all of the basic imaging done the right way.
The live view and the hot pixel issues are a big reason why I gave this camera only 3 stars. Especially so because it is a still camera first.. and if they ship out defective units (with hot pixels) in a brand new box, and ask you to use a software to take 30 minutes just to wait for them to me mapped out, I think they fail to provide the basic purpose of this camera. Plus the live view isnt useful at all except only for shot framing.
Edit to add: Here is a website that will let you compare the sensor quality between different models for your information. I am not sure how reliable they are, but they seem genuine.. still I advise you to interpret that data at your own discretion.
Edit#2: I have since returned this camera and have ordered a Canon 50d. I want to take good still pictures first, and the video mode and the ineffective live view and the LCD werent helping me too much in doing that. Regardless, it may fit your requirements, in which case please enjoy all the features it comes with, and dont forget to share your first video:-) Here are mine:
 The Toast
 The Broom
And heres a link to the video review.
Edit to add: Here is part II of this review, with slight comparison with Canon 50d.