I usually go to
www.scsifaq.org, but it seems to be down today. Just doing a quick search I found
this page. Here's how it breaks down though:
SCSI - 5MB/s
Fast SCSI - 10MB/s
Ultra SCSI - 20MB/s
Ultra2 SCSI - 40MB/s
Wide SCSI - normally SCSI ran at 8 bits, wide boosts it up to 16 or 32 bits. This allows for UltraWide(40MB/s), Ultra2Wide(80MB/s), and Ultra160(160MB/s).
These days, for a hard drive you want at least an UltraWide card, but preferably an Ultra2Wide or greater. For a cdrom or cd burner type drive, fast scsi cards will work just fine.