Search code examples
sql-serverdatabase-designsql-server-2008filegroupphysical-design

Should static database data be in its own Filegroup?


I'm creating a new DB and have a bunch of static data that won't change. If it does, it will be a manual process AND it will happen very rarely.

This data is a mix of varchars and Geographies.

I'm guessing it could be around 100K or so in total, over 4 or so tables.

Questions

  1. Should I put these on a READ ONLY filegroup
  2. Can I create the tables in the designer and define the filegroup during creation? Or is it only possible via a script?
  3. Once the data is in the table (on a read only filegroup), can I change it later? Is it really hard to do that?

thanks.


Solution

  • It is worth it for VLDB (very large databases) for assorted reasons. For 100,000 rows or 100 KB, I wouldn't bother.

    This SQL Server support engineering team article discusses one of the associated "urban legends".

    There is another one (can't find it) where you need 300 GB - 1B of data before you should consider multiple files/filegroups.

    But, to answer specifically

    1. Personal choice (there is no hard and fast rule)
    2. Yes (edit:) In SSMS 2005, design mode, go to Indexes/Key, "data space specfication". The data lives where the clustered index is. WIthout a clustered index, then you can only do it via CREATE TABLE (..) ON filegroup
    3. Yes, but You'll have to ALTER DATABASE myDB MODIFY FILEGROUP foo READ_WRITE with the database in single user exclusive mode