To get the most out of Nvidia graphics cards, you need to download the drivers from nvidia. Because of Nvidia's licencing, these usually can not be included in Linux distributions. Most distros come with the Free nv driver. This works well but only provides basic functionality. It doesn't do 3D graphics acceleration, so can not support some games.
If you're running Ubuntu you should install the linux-restricted-modules-*-nvidia and nvidia-glx Packages, as described on the Ubuntu wiki. If you have an older TNT/TNT2/GeForce2 card you will want linux-restricted-modules-*-nvidia-legacy and nvidia-glx-legacy packages instead. You might also want the nivdia-settings package which adds things like gamma correction to the control panel, or the nvclock package if you want to try overclocking your graphics card.
Read the README file available from Nvidia. It has lots of detail on installation and troubleshooting.
To install the drivers, you will probably need the Kernel source files. The driver needs components customised for your particular kernel. Although the installer has some of these for common kernel versions, it usually needs to make one for your kernel, and for this it needs the kernel source. Unless you are short on disk space, it is probably a good idea to have the kernel source installed anyway, as it may be needed to build other software. Make sure the kernel source matches the kernel version you are using. You can check this using these commands (this example is from Mandriva).
$ uname -r 22.214.171.124-12mdk-i686-up-64GB $ rpm -qa | grep kernel kernel-126.96.36.199.12mdk-1-1mdk kernel-source-2.6-188.8.131.52-12mdk
You can now run glxgears in a console to see if it worked.
If the XServer doesn't start, you typically get a black screen or drop back to the command line. Be sure to check the server messages, and if necessary, the log file (/var/log/XFree.0.log or /var/log/Xorg.0.log).
If you use a "vanilla" 2.6.9 LinuxKernel, you will probably need Nvidia driver version 1.0-6629 or later. 6111 and 5336 failed to compile against 2.6.9 for me (JohnMcPherson) due to a symbol that is no longer exported to modules by the kernel.
Sometime in the 0.7xxx series, Nvidia dropped support for TNT, TNT2 and older GeForce2 cards from their unified driver. If you have one of these cards you have to use a "legacy" driver.