SQL SERVER – Making Table Read Only via FileGroup

Earlier I wrote the blog post SQL SERVER – Making Filegroup Read Only, and my regular client of  Comprehensive Database Performance Health Check asked me a follow-up question on the practical use of making the filegroup read only. Well, the primary reason, we make the table read-only is for making table read only.

When you have a table on the filegroup which is read only, the table can’t be modified anymore and it also becomes read only. Here is the complete script for your experiment.

USE [master]
GO
-- Step 1: Add FileGroup
ALTER DATABASE [SQLAuthority] ADD FILEGROUP [ReadOnlyFG]
GO
-- Step 2: Add File to FileGroup
ALTER DATABASE [SQLAuthority] ADD FILE
( NAME = N'ReadOnlyFile', FILENAME = N'D:\data\ReadOnlyFile.ndf')
TO FILEGROUP [ReadOnlyFG]
GO
-- Step 3: Create Table and populate it
USE [SQLAuthority]
GO
CREATE TABLE RegularTable (ID INT)
ON [ReadOnlyFG]
GO
INSERT INTO RegularTable (ID)
VALUES (1), (2), (3)
GO
-- Step 4: Make FileGroup ReadOnly
USE master
GO
ALTER DATABASE [SQLAuthority]
MODIFY FILEGROUP [ReadOnlyFG] READONLY
GO
-- Step 5: Test the ReadOnly Property
USE [SQLAuthority]
GO
INSERT INTO RegularTable (ID)
VALUES (4), (5), (6)
GO
-- Step 6: (Optional) Make FileGroup ReadWrite
USE master
GO
ALTER DATABASE [SQLAuthority]
MODIFY FILEGROUP [ReadOnlyFG] READWRITE
GO

Let me know what you think about it. If it is still not clear, I will be happy to build a video on the same topic so it is easy to understand. You can also make the table read only with the permissions, but personally, I find that method bit more complicated and also that does not prevent you from changing the table.

Well, that’s it for today. If you liked this video, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.

Here are my few recent videos and I would like to know what is your feedback about them.

• One Scan for 3 Count Sum – SQL in Sixty Seconds #178
• SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
• COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176
• COUNT(*) and Index – SQL in Sixty Seconds #175
• Index Scans – Good or Bad? – SQL in Sixty Seconds #174
• Optimize for Ad Hoc Workloads – SQL in Sixty Seconds #173
• Avoid Join Hints – SQL in Sixty Seconds #172
• One Query Many Plans – SQL in Sixty Seconds #171

Reference: Pinal Dave (http://blog.SQLAuthority.com)