The following script illustrates one way to use a read-only file group.

-- Temporary database

CREATE DATABASE [6D79D4CD-D98F-4D75-B3C0-6134DF86BDC2];

GO

-- Add a file group to hold read-only tables

ALTER DATABASE [6D79D4CD-D98F-4D75-B3C0-6134DF86BDC2]

ADD FILEGROUP [FG_READ_ONLY];

GO

-- Add an NDF file to the filegroup

DECLARE @data_path NVARCHAR(256);

SET @data_path =

(

SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)

FROM master.sys.master_files

WHERE database_id = 1

AND [file_id] = 1

);

EXECUTE

(

'ALTER DATABASE [6D79D4CD-D98F-4D75-B3C0-6134DF86BDC2]

ADD FILE

(

NAME = F_Read_Only,

FILENAME = ''' + @data_path + 'F_Read_Only.NDF'',

SIZE = 64MB,

MAXSIZE = 64MB,

FILEGROWTH = 0MB

)

TO FILEGROUP [FG_READ_ONLY];'

);

-- Switch to the new database

USE [6D79D4CD-D98F-4D75-B3C0-6134DF86BDC2];

GO

-- Test table (read-write)

CREATE TABLE dbo.ExistingTable

(

col1 INTEGER IDENTITY

CONSTRAINT [PK dbo.ExistingTable col1]

PRIMARY KEY CLUSTERED

WITH (FILLFACTOR = 100)

ON [PRIMARY]

);

GO

-- Add some rows

INSERT dbo.ExistingTable DEFAULT VALUES;

INSERT dbo.ExistingTable DEFAULT VALUES;

INSERT dbo.ExistingTable DEFAULT VALUES;

GO

-- Show the data

SELECT *

FROM dbo.ExistingTable;

GO

-- Move the data to the new file group

ALTER TABLE dbo.ExistingTable

DROP CONSTRAINT [PK dbo.ExistingTable col1]

WITH (MOVE TO [FG_READ_ONLY]);

GO

-- Re-create the primary key

ALTER TABLE dbo.ExistingTable

ADD CONSTRAINT [PK dbo.ExistingTable col1]

PRIMARY KEY CLUSTERED (col1)

WITH (FILLFACTOR = 100)

ON [FG_READ_ONLY];

-- Now make the file group read only

ALTER DATABASE [6D79D4CD-D98F-4D75-B3C0-6134DF86BDC2]

MODIFY FILEGROUP [FG_READ_ONLY]

READ_ONLY;

-- Show the data

SELECT *

FROM dbo.ExistingTable;

GO

-- Modifications fail now with the message:

-- (The index ... resides on a read-only filegroup ("FG_READ_ONLY"), which cannot be modified.

INSERT dbo.ExistingTable DEFAULT VALUES;

GO

-- Tidy up

USE [master];

DROP DATABASE [6D79D4CD-D98F-4D75-B3C0-6134DF86BDC2];