Currently, SATA drives are pretty much the same old ATA drives you're used to seeing, except with a new way of getting the data in and out. The notable oddball is the Western Digital 10000rpm "Raptor" model, which is like some sort of SCSI-wannabe drive (it has quick seeks and a 10000rpm spindle speed). SATA has slim cables with fewer connectors.
Serial ATA has a theoretical peak throughput of 150 megabytes (= 1200 mega
bits) per second. USB 2.0 has a theoretical peak throughput of 60 megabytes (= 480 mega
bits) per second, and current-generation Firewire has a theoretical peak throughput of 50 megabytes (= 400 mega
bits) per second. In theory, SATA equipment has the potential to be hot-pluggable, meaning you can unplug a drive while the system's running, but on desktop boards it's probably not a good idea.
Recent-model SCSI is the
real thing, with 160 or 320 megabytes per second per SCSI channel, up to 14 drives per cable, up to 10-meter bus lengths (internal and/or external), command queueing, extremely low seek times, speeds of 10000rpm or 15000rpm, and generally these drives are built (and warranted) for five years of 24/7/365 server abuse.
Bottom line, pick SATA if you want the slim cables or if you want to get a Raptor and improve performance without investing in a SCSI setup.