Hi Team,

I am going to create a new database, is it best practice to create all tables in PRIMARY File Group and all indexes of those tables in new user defined/Secondary File Group?   

FYI.. Among all the tables, two tables in my database has more than 10Million Records.  

Thanks,

Ali

Hello,

Separating indexes from data onto separate filegroups may improve performance if there are on different (physical) disks. You can also try to partitioning the table or index spread across multiple physical disks which may improve query performance. However, there is no filegroup strategy that is optimal for all cases.

You can refer to the discussion in the following thread about filegroup strategy of data and index:
http://dba.stackexchange.com/questions/2626/when-should-nonclustered-indexes-be-stored-on-separate-filegroups

Regards,
Fanny Liu