Limitation of Truncate

TRUNCATE TABLE removes all rows from a table by deallocating data pages. It is DDL, not DML. If we see Performance wise then it is better than delete but it has below limitations.

Ø  It cannot be used If table has a FOREIGN KEY Reference

Ø  It cannot be used on tables with Indexed Views

Ø  It cannot be used on tables with Replication

Ø  It cannot be used on tables with Change Data Capture (CDC)

Ø   WHERE we can’t use

Ø  It cannot Fire DELETE Triggers

Ø  It resets IDENTITY (Sometimes Undesired)

Ø  It requires ALTER Permission

Ø  It is Minimally Logged (No Row-Level Recovery) hence we cannot recover individual rows

Let’s see the demo one by one.

It cannot be used If table has a FOREIGN KEY Reference

For this demo we will create below table and inserting some record.

CREATE TABLE Parent (     ID INT PRIMARY KEY );  CREATE TABLE Child (     ID INT,     ParentID INT         FOREIGN KEY REFERENCES Parent(ID) );

Table has created. See below.

Even though we don’t have data in the parent table we can’t truncate this table. We will get an error. See below.

 

It cannot be used on tables with Indexed Views

For this demo creating a table and inserting some records after that we are creating a indexed view (materialize view).

CREATE TABLE Sales (     SaleID     INT IDENTITY PRIMARY KEY,     ProductID  INT NOT NULL,     Quantity   INT NOT NULL,     Amount     DECIMAL(10,2) NOT NULL,     SaleDate   DATE NOT NULL ); --inserting few records INSERT INTO Sales (ProductID, Quantity, Amount, SaleDate) VALUES (1, 2, 200, '2024-01-01'), (1, 3, 300, '2024-01-02'), (2, 1, 150, '2024-01-01'), (2, 4, 600, '2024-01-03');

See the record into the table

Now we are creating the view

--creating view with schema bind CREATE VIEW vw_ProductSalesSummary WITH SCHEMABINDING AS SELECT     ProductID,     COUNT_BIG(*) AS TotalOrders,     SUM(Quantity) AS TotalQuantity,     SUM(Amount) AS TotalSales FROM dbo.Sales GROUP BY ProductID; GO

See the data in view

This view is not yet indexed view because we don’t have any index on this view. Now we are creating index on this view. 

CREATE UNIQUE CLUSTERED INDEX IX_vw_ProductSalesSummary ON vw_ProductSalesSummary (ProductID); GO

 Now the view becomes an Indexed View and Data is physically stored.

Let’s truncate the parent table.

truncate table Sales

So we can’t truncate this table.

But we can delete the records from this table.

Data has been deleted successfully.

It cannot be used on tables with Replication

Before seeing the demo first, we will see what is Replication

 Replication in SQL Server is a feature that allows us to copy and synchronize data from one database to another. The source database is called the Publisher, and the destination database is called the Subscriber. Replication keeps data in sync, either periodically or near real-time, depending on the type of replication.

Replication tracks row-level changes using the transaction log, and TRUNCATE does not log row-level deletes — it deallocates data pages, which replication cannot capture or replay.

Core concept of Replication Works

In Transactional Replication (most common):

Ø  SQL Server uses the transaction log

Ø  The Log Reader Agent reads row-by-row DML operations:

o   INSERT

o   UPDATE

o   DELETE

Ø  These operations are converted into commands

Ø  Commands are replayed on the Subscriber

Truncate is DDL operation.

How TRUNCATE Works Internally

TRUNCATE TABLE is:

Ø  A DDL operation

Ø  Does NOT delete rows one by one

Ø  Instead:

o   Deallocates entire data pages

o   Resets IAM pages

o   Resets identity

Ø  Logs page deallocation, not row deletes.

Due to that we can’t truncate the table.

When we will truncate the table, we will get the below error.

It cannot be used on tables with Change Data Capture (CDC)

Truncate will not work in CDC table. CDC captures row-level changes from the transaction log, and TRUNCATE does not log row-level deletes — it deallocates pages, which CDC cannot interpret or record. 

How CDC Works Internally

Change Data Capture (CDC):

Ø  Reads the transaction log

Ø  Uses the CDC Log Reader Agent

Ø  Captures row-level DML operations:

o   INSERT

o   UPDATE

o   DELETE

Ø  Stores changes in CDC change tables (cdc.<table>_CT)

Read more about CDC here: Change data capture (CDC) in sql server

https://bageshkumarbagi-msbi.blogspot.com/2015/10/change-data-capture-cdc-in-sql-server.html

let’s see the quick demo.

creating a table and enabling the CDC on this table.

create table EMP_CDC_Demo ( id int , nm varchar(50), emp_add varchar(50), Mob varchar(10) )

Enabling the CDC on the table.

EXEC sys.sp_cdc_enable_db --enabling CDC on the table level Exec sys.sp_cdc_enable_table @source_schema=N'dbo', @source_name=N'EMP_CDC_Demo', @role_name=NULL

 CDC has been enabled. Few Systems table has been created. See below

Now inserting few records.

INSERT INTO EMP_CDC_Demo (id,nm,emp_add,Mob)   VALUES ('1','Kumar','Pune','8888802459')

See the records in table and CDC Table.

If we do any update or delete in that case we will get the changed data into CT table.

Here now we are trying to truncate the EMP_CDC_Demo table

truncate table EMP_CDC_Demo

 

But we can delete the records from this table.

 WHERE clause we can’t use with Truncate

We can’t use where clause in the Truncate statement. It doesn’t allow us.

See below

Now we are using where clause in the truncate statement.

It will throw an error.

It cannot Fire DELETE Triggers

Since Truncate is a DDL operating, Triggers fires only on DML (Insert/update/delete) Operation. Due to that when we truncate the table Trigger is not faired.

See the demo. for this demo we will create two table and one delete trigger.

--Creating table create table Employee ( EmpID int identity(1,1) not null primary key, Ename varchar(50) null, Eaddress varchar(10) null, EMob varchar(10) null ) --Inserting more than 10 lacks records._ DECLARE @c int set @c=0 while @c<=100 BEGIN insert into Employee values('Bagesh Kumar Singh','pune','8888802459') set @c=@c+1 END

Now creating a audit table

CREATE TABLE Employee_DeleteLog (     EmpID INT,     Ename VARCHAR(50),     Eaddress VARCHAR(100),     EMob VARCHAR(10),     DeleteUser varchar(50) default suser_sname(),     DeletedDate DATETIME  default getdate() );

Creating Trigger

CREATE TRIGGER trg_LogDeletedEmployee ON Employee AFTER DELETE AS BEGIN     INSERT INTO Employee_DeleteLog (EmpID, Ename, Eaddress,EMob)     SELECT         d.EmpID,         d.Ename,         d.Eaddress, d.EMob FROM deleted d; END;

 Deleting few records from the table.

Record delete and stored in the audit log table.

Now we are trying to truncate this table.

Truncate will not work.

 TRUNCATE Requires ALTER Permission

Since truncate is DDL command to perform the truncate table, we need to have ALTER permission of the table.