次の方法で共有

How to PREVENT TRUNCATION of TABLES?

  • [アーティクル]

One of my clients’ requirement to prevent the TRUNCATION of tables on the database by their applications led me to the research on this topic. We helped them with the information related to the DELETE of records by configuring SQL server audit where we get the data related to the DELETE operation in the Audit tables.

In comparison with the DELETE statement, TRUNCATE TABLE has the following advantages:

  • Without exception, zero pages are left in the table.

After a DELETE statement is executed, the table can still contain empty pages. For example, empty pages in a heap cannot be deallocated without at least an exclusive (LCK_M_X) table lock. If the delete operation does not use a table lock, the table (heap) will contain many empty pages. For indexes, the delete operation can leave empty pages behind, although these pages will be deallocated quickly by a background cleanup process.

TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table definition in addition to its data, use the DROP TABLE statement.

 

  • Fewer locks are typically used.

When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table (including a schema (SCH-M) lock) and page but not each row.

 

  • Less transaction log space is used.

The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

 

With all these advantages on the performance aspect, TRUCATE TABLE statement has its own pitfalls because the operation is minimally logged and it happens almost instantly. However, being minimally logged means that individual record deletions are not recorded in the transaction log. To avoid some of these pitfalls, we can implement a safeguard to prevent table truncation. Here are the scenarios where we cannot use TRUNCATE TABLE:

  • On tables that are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)

 

  • On tables that participate in an indexed view.

 

  • Also by keeping a toll on the permissions The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, it is not a sure shot prevention measure as we can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause.

 

  • On tables that are enabled for Change Data Capture (CDC) because CDC reads the transaction log to monitor data changes and truncation does not write delete records to the log. Also, enabling CDC is a heavy solution just to prevent truncation as it is an Enterprise Edition feature. An attempt to truncate a table enabled for CDC will result in message 4711:

Msg 4711, Level 16, State 1, Line 1
Cannot truncate table 'tbl' because it is published for replication or enabled for Change Data Capture.

 

  • Also on tables that are published by enabling transactional replication or merge replication. Even this feels like an expensive measure and not a reliable technique to protect the data as tables within replication operations can still be truncated.

 

Analyzing all these options closely, leaves only the following two options:

  • To create a foreign key constraint.

If the table has a logical relationship with another table, then enabling a foreign key between them is probably the best answer as there is virtually no cost involved. However, if no logical relationship exists, we could create an empty table simply for the purpose of creating a foreign key. Here is a demonstrate for this approach:

 

-- Set the database context
USE [database];

-- If the table already exists, drop it
IF OBJECT_ID(N'dbo.tbl', N'U') IS NOT NULL DROP TABLE dbo.tbl;

-- Create a table
CREATE TABLE dbo.tbl (
ID INT IDENTITY(1,1) NOT NULL
, Value VARCHAR(20) NOT NULL
, CONSTRAINT PK_tbl PRIMARY KEY CLUSTERED (ID)
)
;
GO

-- If the dummy foreign key table already exists, drop it
IF OBJECT_ID(N'dbo.tblFK', N'U') IS NOT NULL DROP TABLE dbo.tblFK;

-- Create the dummy foreign key table
CREATE TABLE dbo.tblFK (
ID INT IDENTITY(1,1) NOT NULL
, IDFK INT NOT NULL
, CONSTRAINT FK_tbl_tblFK FOREIGN KEY (IDFK) REFERENCES dbo.tbl(ID)
)
;
GO

-- Attempt truncation
TRUNCATE TABLE tbl;

Truncation will fail with error 4712:

Msg 4712, Level 16, State 1, Line 2
Cannot truncate table 'tbl' because it is being referenced by a FOREIGN KEY constraint.

  • To include the table in an indexed view.

Even this is as simple as the previous, and it has the same drawback of creating a “dummy” object in the database. However, it also has the disadvantage of consuming more disk space as the index has to be stored. Here is a demonstrate for this approach:

 

-- Set the database context
USE [database];

-- If the dummy indexed view already exists, drop it
IF OBJECT_ID(N'dbo.vwTbl', N'V') IS NOT NULL DROP VIEW dbo.vwTbl;
GO

-- Create a view with schemabinding (necessary for indexing)
CREATE VIEW dbo.vwTbl
WITH SCHEMABINDING
AS
SELECT ID, Value
FROM dbo.tbl;
GO

-- Index the view
CREATE UNIQUE CLUSTERED INDEX UIX_vwTbl
ON dbo.vwTbl(ID);
GO

-- Drop the dummy foreign key table
IF OBJECT_ID(N'dbo.tblFK', N'U') IS NOT NULL DROP TABLE dbo.tblFK;

-- Truncation will fail because the table is referenced by an indexed view
TRUNCATE TABLE tbl;

Truncation will fail with error 3729:

Msg 3729, Level 16, State 2, Line 2
Cannot TRUNCATE TABLE 'tbl' because it is being referenced by object 'vwTbl'.

Note:

I came across the usage of DDL trigger also as an option to avoid TRUNCATION, however there is no TRUNCATE TABLE event defined in SQL Server. And, while a truncation is effectively a drop and recreate of the table, it does not fire the DROP TABLE event. Thus, a DDL trigger will not work.

Hope this helps.. Happy truncating!!