Home Forums SQL Server 2005 Administering Database Design: Should we have Multiple Data files and Multiple File Groups – SQL Server 2005 RE: Database Design: Should we have Multiple Data files and Multiple File Groups – SQL Server 2005

  • You are right in what you say about separating the system data and user data, and many experts in SQL Server database design say this is a good idea.

    However in practice, most people do not bother doing this.

    If you do want to split your system data and user data, then set up the second filegroup as you suggest. Then mark the second filegroup as the Default filegroup (Google can find the right command), and all further database objects will automatically be put in the second filegroup.

    If the CREATE statement for a new object explicitly requests it goes into the Primary filegroup, there is nothing you can do to prevent this, but if no data placement clause is given (which is the normal situation), then it will automatically go into the second filegroup.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara