Sql Server - How many files in a database

Tempdb seems to be a special animal as they address is specifically.

http://msdn.microsoft.com/en-us/library/ee377054%28BTS.10%29.aspx

Splitting the TempDB files across multiple files may resolve performance issues related to I/O operations. As a general guideline, create one file data file per processor and use the same size for all files created.

Most other databases should have their data and logs on different drives. These drives will most likely be a RAID configuration, so you are in fact using more than one drive behind the scenes.

If you have a hot table, you can move that to another filegroup and put that on a separate disk group.

A certain performance monitoring tool I have been using makes recommendations to split databases into files that equal the number of processors on the machine. I don't remember reading that in any MS documentation though.