Originally posted by: PaulMcKibben
I understand how to create a secondary file. I have not worked with them before so my question should be:
Once secondary files are created, how does SQL "know" to put/get the data from the secondary files?
There are 2 considerations
You can have a second physical file that's part of your primary file group. This allows you to spread the data over multiple drive letters. You can do this on the fly, and when the first file, sucah as E:\....\DATA.MDF fills up, it will spill over automatically into F:\....\DATA_2.NDF. There is no logical splie, SQL still sees it as one big file and tables and indexes are randomly spread across both, but you get a performance improvement because you have 2 drives instead of 1.
You can also create a new FILEGROUP with it's own data file. This is separate from the PRIMARY filegroup and you have to explicitly put objects into it, because the PRIMARY is the default. For instance, I just created a secondary FILEGROUP called INDEX_GROUP, and within that I created INDEX.DNF. On the weekend I will move indexes out of the PRIMARY file group and into INDEX_GROUP on a new drive. This will help performance by leaving data on one drive and putting indexes on another drive.
Some people also split data into different file groups on different drives. You might put a heavily accessed ORDERS table in E:\....\DATA.MDF , and CUSTOMERS in F:\....\DATA.MDF Then when you do big, frequent joins between the 2, you will get the benefit of the data on 2 separate drives for better performance.