Siusic Dot Com

I knew for a long time that you can use file groups to place the data and index of a table on separate files and physical disks so to improve database performance. I didn’t know that we can place data and index onto different files in the same file groups until I read this article. The article gives really great tips on how to use SQL server file groups. However I don’t understand the following. It says,

For very large database (VLDB), tables and their related indexes should be separated onto separate files and physical disks for optimum performance running queries, but not separate filegroups. If they are in separate filegroups, then you cannot back up and restore them as a single unit.

As far as I know

the only way to place data and indexes of a table on different files is to put them on different file groups and those file groups can be backup and restored without problems. Even though data and indexes are in separated backup files you can still restore the database by restoring the file backups one by one. As long as the log backup is available the database can be recover. I will talk about backups in later post.

So I did some researches and concluded that was a false statement. Basically there is no way you can tell SQL server to create table or index on a specific file. You can only tell SQL server to create them on specific file groups. However you can create a file group that has different files that are located on separated physical disks. In that way the data of a table and its related indexes created in on this file group can spread over multiple physical disks. It also improves performance and it allows you to backup and restore data and indexes as a single unit. I believe this what this statement mean to say.

Related Posts:

A Fast Way to Update Large Amount of Data in SQL Server With Select Into Statement

A Useful Script to Get The Disk Space Used by Every Table

Filegroup

MySQL vs SQL Server in Data File Management

What is Fragmentation and How to Defragment a Table Being Used Consistently?