Every file group except the primary file group can be tagged as read-only. If the entire database is read-only, the primary file group can be set as read-only. INSERT, UPDATE and DELETE statements executed against objects stored in read-only file groups will fail any way. Any statement attempting to create an object on the read-only file group will also fail. Note, however, that you can modify the structure of the existing objects residing on the read-only file group. For example, you can add or drop columns from an existing table.
SQL Server does not lock any resources while executing SELECT statements against a read-only file group. SELECT statements against objects in a read-only file group can be somewhat faster than the same statements executed against objects in a read-write file group.
You can tag a file group as read-only using ALTER DATABASE statement. Before you modify the file group you must ensure that no other users are connected to the database. The following statements place customers database in a single-user mode and mark FG2003H1 file group as read-only:
USE [master] GO ALTER DATABASE [customers] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO ALTER DATABASE [customers] MODIFY FILEGROUP [FG2003H1] READONLY GO ALTER DATABASE [customers] SET MULTI_USER Go
If later, you need to make changes back for this file group:
ALTER DATABASE [customers] MODIFY FILEGROUP [FG2003H1] READWRITE
Read-only file groups can be compressed which can help you reserve more disk space for files containing write-intensive data. SQL Server supports only Windows NTFS compression. Using compression doesn’t require any changes to backup / restore operations performed against the file group. However, that SELECT statements going against compressed file groups will be slower than the same statements going against uncompressed files because SQL Server need more time to de-compress the data.