Different ways to make a table read-only | SansSQL

Tuesday, December 24, 2013

Different ways to make a table read-only

There may be many cases where a tables needs to be made as read-only in order to maintain the existing data. This can be achieved using many ways and out of which the common methods are
  • Deny Permission
  • Put the table on read only file group
  • Create trigger instead of insert, update, delete
  • Make the database read-only
  • Create a Column Store Index

Deny Permission on the table:
In this method, the insert, update and delete permission will be denied on the table for a particular login.
DENY INSERT, UPDATE, DELETE ON <TableName> TO <DBUserName>

Put the table on a Read-Only file group:
In this method, you have to create a read-only file group and move the table to this file group.
USE [master]
GO
ALTER DATABASE SansSQL ADD FILEGROUP [Read_Only]
GO
ALTER DATABASE SansSQL ADD FILE ( NAME = N'readonly_table', FILENAME = N'D:\Databases\SansSQL.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Read_Only]
GO
/*
Create or move a table to this new file group
*/
--Make the file group Read-Only
ALTER DATABASE SansSQL MODIFY FILEGROUP [READ_ONLY_TBLS] READONLY

Create trigger instead of insert, update, delete:
In this method, you will create a trigger which performs instead of actions
CREATE TRIGGER tr_maketblRO ON <TableName>
    INSTEAD OF INSERT,
               UPDATE,
               DELETE
AS
BEGIN
    RAISERROR( 'Table is read only.', 16, 1 )
    ROLLBACK TRANSACTION
END

Make the database read-only:
In this method, you will make the entire database read-only
USE [master]
GO
ALTER DATABASE SansSQL SET READ_ONLY WITH ROLLBACK IMMEDIATE
GO

Create a Column Store Index:
From SQL Server 2012 onwards, there is a new index available called column store index. When this index is created ona table, it makes the table read-only giving high performance for data reads. This is normally useful in data warehouse environments.
USE [SansSQL]
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX <IndexName> ON <TableName>
(
 <Column List>
)

6 comments:

MonicaBrown said...

Thank you, I just needed this information for my essay. Since I don't know how to write on my own, I started looking for an essay writing service. I always wondered where to find a good service. I was advised domyessay reviews https://nocramming.com/domyessay-review. You can be sure that we will not be cheated, there will be no plagiarism and everything will be done in the best way. If you need help in writing an essay, you can safely contact them.

Robin C. said...

Creating a trigger works much better for different development environments and is in fact just more universal than any other methods described. For example I've used a similar trigger at https://stateofwriting.com/uk/write-my-essay to create a table that will perfectly work at any device because it is made responsive.

DarenTill said...
This comment has been removed by the author.
DarenTill said...

In order to identify errors that affect the tone of your text work, all you have to do is start using a specialized tool! This tool was developed specifically to simplify the process of checking and identifying errors. I regularly use this tool, and I am satisfied with the results of its work!
https://www.essaytones.com/essay-tone-checker-for-different-types-of-essays/

partofspeechfinder said...

Hi everyone! Adverbs change words and can take the form of a complete sentence, phrase, or simply a single word. The adjective checker application has robust functionality that generates flawless sentences, making the essay, dissertation, or article easier to read. So dive in and discover more about our adverb replacer, how to employ adverbs in a sentence, typical adverb-related errors, and more!

Janet Locane said...

It seems to me that everyone has encountered difficulties when making a table. I needed to create many of these elements for my course work. And I didn’t manage to do it on time. That’s why I started using the nursing essay writing service in such situations. Thanks to the writers, I was able to complete everything according to the curriculum in a short time.

Post a Comment

Ads