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)
Hi Pinal, Thanks for sharing a handy tip however, I believe this “read-only” provision will apply to all tables on that particular Database at once, right? However, incase I have 50 tables in a DB and I want to apply it on few selective tables (say 30 tables) then I have to create different FILEGROUPS for different tables separately OR Have I will achieve it if FILEGROUP is at Database-Level and not at Tables-Level?