Using files and filegroups

By placing certain database objects on different physical disks, you can improve the performance of your databases. But, how do we control the placement of certain database objects on particular physical disks?

Well, files and filegroups are used in SQL Server to physically organize your database files and database objects. By organizing data files with the help of filegroups, you can place specific database objects, such as tables and indexes, on particular physical disks.

Let's say that you are responsible for creating and designing a new production database, which will be accessed by many applications. You expect one particular table to grow very large with time. You are lucky enough to have enough hardware resources in the form of multiple physical disks that you can use to distribute your database physically. You decide to place the table that you estimate will become very large and expect will be accessed heavily by many requests, so that you can achieve the...