Freezing a Table on SQL Server
Introduction
This article provides a way to maintain one or more tables in a database without the possibility of change to the structure or your data, although "SQL Server User" can make this changes through a GRANT permission.
This condition is useful for some types of information, such as:
- Purgue Tables (information that still needs to be retained, but no more available in the main table).
E.g. Requests for Purchase and Sales of Products made in previous years; - Historic Tables (where we have maintained data for months or years) to link with data from several events on the same date.
E.g. Water level of a river during certain months. This is useful as a quick reference navigation;
All this information can help prove a service already done for a customer, can be used as a reference for analysis of historical trends and comparing performance over a period of time which is important for decision-making strategies in various companies.
Thus, we must ensure that these data should not be changed to maintain the credibility of the information storage.
Building the Environment for Testing
So we can demonstrate this example will create a Historic Customers table, but to freeze table we create a Secondary file to the Database on a new FileGroup named "FREEZEFILEGROUP".
Before you start this demo, we can identify in the image below that this new FileGroup is part of the same Database, but in a .NDF file that separating data that intend to isolate and become "read-only".
So the Database keeps "PRIMARY" FileGroup as default (where is linked .MDF file), for all tables that you manipulate data through INSERT, UPDATE or DELETE statements, but in both FileGroups, we can data query using a SELECT statement.
In T-SQL script below, we can see the FileGroup creation and follow the .NDF file creation, linked to this new FileGroup.
For demo use data between the tables, we created "TB_CUSTOMER" table on "PRIMARY" FileGroup, where is data stored and also "TB_CUSTOMER_HISTORIC" table on "FREEZEFILEGROUP", where we will move the old data to be "frozen".
We split the data based on the year indicated in the DT_CREATED column of the "TB_CUSTOMER" table.
See the image below for creation of new FileGroup
See below T-SQL script
USE master;
GO
--CREATE A NEW FILEGROUP
ALTER DATABASE WI_Infra
ADD FILEGROUP FREEZEFILEGROUP;
GO
ALTER DATABASE WI_Infra
ADD FILE (
NAME = 'YourDatabaseHistoric',
FILENAME = 'F:\Bancos\WI_InfraHistorico.ndf',
SIZE = 5MB,
MAXSIZE = 800MB,
FILEGROWTH = 2MB
) TO FILEGROUP FREEZEFILEGROUP;
GO
USE WI_Infra
GO
--CREATING A SAMPLE TABLE INTO "PRIMARY" FILEGROUP
CREATE TABLE TB_CUSTOMER (
CD_CUSTOMER int NOT NULL PRIMARY KEY CLUSTERED,
NM_CUSTOMER varchar(150) NOT NULL,
DT_CREATED datetime NOT NULL
) ON [PRIMARY];
GO
--CREATING A SAMPLE "HISTORIC DATA" TABLE INTO "PRIMARY" FILEGROUP
CREATE TABLE TB_CUSTOMER_HISTORIC (
CD_CUSTOMER int NOT NULL PRIMARY KEY CLUSTERED,
NM_CUSTOMER varchar(150) NOT NULL,
DT_CREATED datetime NOT NULL
) ON [FREEZEFILEGROUP];
GO
INSERT INTO TB_CUSTOMER VALUES (1, 'CUSTOMER 1', '2010-10-01');
INSERT INTO TB_CUSTOMER VALUES (2, 'CUSTOMER 2', '2011-10-01');
INSERT INTO TB_CUSTOMER VALUES (3, 'CUSTOMER 3', '2012-10-01');
INSERT INTO TB_CUSTOMER VALUES (4, 'CUSTOMER 4', '2013-10-01');
INSERT INTO TB_CUSTOMER VALUES (5, 'CUSTOMER 5', '2014-10-01');
GO
Moving Old Data
Now with the Database structure built, we can move data that are no longer used on "TB_CUSTOMER" table to new Historic table. In this case we determined a cutoff date. The data will be separate from "TB_CUSTOMER" table to insert on "TB_CUSTOMER_HISTORIC" table.
It's important to keep this type of manipulation secure data during execution, then always use a SQL Transaction to ensure that our data is not lost.
See in T-SQL script below that after inserting the old data into "TB_CUSTOMER_HISTORIC" table, these same data are deleted from "TB_CUSTOMER" table, and all this data manipulation will be effective(COMMIT TRAN) only if no error occurs, that is @@ERROR variable must be equal to zero.
**See image below
**
See this T-SQL script
USE WI_Infra;
GO
--MOVING OLD CUSTOMER DATA TO "HISTORIC" TABLE
BEGIN TRAN
INSERT INTO TB_CUSTOMER_HISTORIC
SELECT * FROM TB_CUSTOMER WHERE DT_CREATED < '2013-01-01';
DELETE FROM TB_CUSTOMER WHERE DT_CREATED < '2013-01-01';
IF @@ERROR = 0
BEGIN
COMMIT TRAN
END
ELSE
BEGIN
ROLLBACK TRAN
END
GO
--QUERYING DATA
SELECT * FROM TB_CUSTOMER;
SELECT * FROM TB_CUSTOMER_HISTORIC;
GO
Freezing Table
Now that old data has been moved to "TB_CUSTOMER_HISTORIC" table, we change the "FREEZEFILEGROUP" FileGroup status to "read-only" and make data available only for queries as the display on T-SQL script below.
**See T-SQL script below in this image
**
See below T-SQL script
--CHANGE FILEGROUP AS "READ ONLY"
USE master;
GO
ALTER DATABASE WI_Infra
MODIFY FILEGROUP [FREEZEFILEGROUP] READONLY;
GO
So to check if this data is really protected against changes and holding it's current state(structure and data), we can execute the DELETE statement to exclude one row of the "TB_CUSTOMER_HISTORIC" table.
**See image below (click to enlarge)
** 
See T-SQL script below****
USE WI_Infra;
GO
--TRYING TO DELETE A ROW AFTER MODIFY FILEGROUP
DELETE FROM TB_CUSTOMER_HISTORIC WHERE CD_CUSTOMER = 3;
GO
After executing the DELETE statement, an error message is raised, indicating that "TB_CUSTOMER_HISTORIC" table is inside a "Read-Only" FileGroup and so, no rows can be affected.
Conclusion
The past information can be very important for different conditions system analysis. Isolate these old data so as to ensure that this information is really full and was not affected at any time. This is essential for the credibility of different business types.
Using a specific FileGroup for this task makes it a simple process, but effective. There is no chance of an SQL User (same elevated permissions) can modify or remove one or more rows on Table.
This provides greater security in data storage and easing their adjustments when necessary.