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>
)

1 comment:

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.

Post a Comment

Ads