As I recall, NCQ was developed for use in server settings where SCSI is not being used. The reason for not SCSI is that it already had an implementation in place, whereas SATA did not. It was (I believe) developed to bring better performance to systems where there is nearly constant hard disk access, such as in a server setting. Otherwise, it makes no difference, especially if your SATA controller has it in the hardware.
Basically its a completely pointless feature for the desktop. If there's no difference in price, and there's no difference in feature sets (barring he NCQ) go ahead and get it, but if there's even a dollar difference, it may require some deeper thought as to whether or not to get it.
That said, I have a drive with NCQ and I don't see the differences. Its just much faster than my older ata133 drive that it works next to.