See http://msdn.microsoft.com/en-us/library/ms345146(v=sql.90).aspx for a good description. The MS white paper http://download.microsoft.com/download/D/B/D/DBDE7972-1EB9-470A-BA18-58849DB3EB3B/PartTableAndIndexStrat.docx also has very good information.

In a nutshell,

-- Multiple file groups allow you to better split up & load balance I/O activity across multiple physical disks, especially if you set up each file group on a different disk drive.

-- Multiple files allow parallel activity at the Windows level.

-- File groups work at the SQL Server level. They aid in managing your physical space more easily.

-- Using different disk drives for each file allow parallelism at the hardware level (multiple spindles spinning and accessing data).

I like setting up a one file group for each partition. This makes management a little easier for me, since a partition & a file group are synonymous, and this way you can spread a query across multiple physical disk spindles.

Even if you don't have enough physical drives to allocate one file group/partition per drive, set up one file group for each partition anyway, and if needed, I'll double up the least heavily used partitons/filegroups, but try and allocate the most frequently used "hot spots" to their own dedicated phyical disks.

Hope that helps.

G**2