SQL Training Q&A - 8
Q) If you set up a database with 3 files, will SQL Server fill the first file before using the other 2 or will it store data in all 3 files right from the beginning? Is it related to the initial size or the growth setting of the file? What if data is deleted? What if it’s three filegroups with one file each?
One of the main ideas behind using multiple data files is to allow SQL to work spread the data across the multiple files. All other things being equal, if you have a database with four data files stored in four disks instead of a single data file on a single disk, SQL will be able to read and write the data faster. The data will be spread across the multiple files and SQL will work with all the files.
You will see that, over time, the multiple files will end up with roughly the same size, unless you run out of disk space in that volume. It's true that you will get the best performance if you pre-allocate space instead of letting the files grow.
Most SANs, intelligent controllers and even Windows Server itself can implement the striping of the data in multiple disks at a lower level (this is usually called RAID 0), but if for some reason you can't leverage that, SQL can help with the multiple data file setup. You can also combine the options for further striping.
Filegroups, in the other hand, are used to allow you to control where specific objects in your database are stored. You could, for instance, have a table with historical data stored in a separate filegroup that sits on a slower disk. You can backup and restore file groups separately, too.
For more details, check http://msdn2.microsoft.com/en-us/library/ms187087.aspx