Is it possible to make any table read only? Well, not directly but you can for sure do that by making filegroup read only. I was asked this in my recent Comprehensive Database Performance Health Check. Let us see the solution.
Here is the script to make any filegroup a read only filegroup.
ALTER DATABASE DBName MODIFY FILEGROUP Read_Only READONLY
Once the filegroup is read only, you can read the data from the table on the same filegroup but can’t insert, update, or delete. I hope you like this little trick.
If you want to bring back the filegroup to readwrite, you can execute the following command.
ALTER DATABASE DBName MODIFY FILEGROUP Read_Only READWRITE
You can also do this from the database properties and filegroup tab.
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)
