Explain the concepts behind placing indexes with filegroups.
Indexes are created on the same filegroup as the base table on which the index is created. However, one can achieve performance gains by creating the index on a different filegroup if the filegroup makes use of different physical drives with their own controllers. Data and index information both can be thus read in parallel by multiple disk heads. It improves performance as both file groups are being completely used without any contention.
Explain the concepts behind placing indexes with filegroups.
Indexes can be placed on different file groups to increase performance. By default, indexes are created on the same file group as the base table. Placing indexes with different filegroups are more commonly used when type of access cannot be predicted. Spreading the index across different file groups can be done to achieve this. This also ensures that all disks are being accessed since all data and indexes are spread evenly across all disks.
Example of creating an index on a specific file group:CREATE UNIQUE CLUSTERED INDEX sample ON t1.c
