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