Prevent TRUNCATE TABLE

The ability to truncate a table is a powerful tool. Truncation removes all data from a table, and, because the operation is minimally logged, it happens almost instantly. However, being minimally logged means that individual record deletions are not recorded in the transaction log. There are other potential pitfalls from truncation as well. Can someone suggest a few?

To avoid some of these pitfalls, we can implement a safeguard to prevent table truncation. As BOL states, truncating is not allowed on tables that:

  • Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)
  • Participate in an indexed view.
  • Are published by using transactional replication or merge replication.

In addition to these three conditions, it is also true that when a table is enabled for Change Data Capture (CDC) it cannot be truncated. Why? Because CDC reads the transaction log to monitor data changes, and, as mentioned above, truncation does not write record deletes to the log. 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.

So, which of these four options should we use? Enabling CDC seems like a heavy solution simply to prevent truncation, plus it is only available at Enterprise Edition. Enabling replication also feels like an out-sized answer; plus, tables can be truncated within replication operations, so that does not provide reliable protection for the data.

That leaves two options: create a foreign key constraint, or include the table in an indexed view. 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.

Let’s demonstrate that approach first.

-- Set the database context
USE [db];

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

We could also create an indexed view of the table. This approach 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. Why? Because the index has to be stored! But that may not be a bad thing… if you have a secondary use for the view.

Let’s demonstrate this approach as well, re-using the sample table we created previously.

-- 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'.

So, we just walked through a few options to prevent table truncation. What about using a DDL trigger on the database? Well, for whatever reason, 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.

Does anyone have any other ideas or real-world experience with preventing TRUNCATE TABLE operations? Please send them in via a comment.

As ever, don’t forget to clean-up after yourself!

-- Drop the demonstration objects 
IF OBJECT_ID(N'dbo.vwTbl', N'V') IS NOT NULL DROP VIEW dbo.vwTbl;
IF OBJECT_ID(N'dbo.tbl', N'U') IS NOT NULL DROP TABLE dbo.tbl;

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: