Difference Between Truncate and Delete Command in SQL Server

I have seen a lot of times, people get confused to differentiate the Truncate and Delete commands in SQL Server. If you are a DB developer or a DBA, then this is a mandated requirement to know exactly what is being performed when Truncate on a table and delete on a table. Let’s check the difference between Truncate and Delete in SQL Server.

TRUNCATE

TRUNCATE is a DDL command, which is used to delete all the rows of a table without being to use a WHERE clause. Most of them come up with an assumption that a truncate cannot be rolled back once the TRUNCATE command is executed. But, the assumption is very untrue! The TRUNCATE command can be rolled back with the lopping into a begin transaction and rollback commands.

Further, in truncation, the pages are completely deallocated and commit to the transaction log as deallocation. TRUNCATE can be only executed if login is given with an ALTER permission for a database. Additionally, as the rows are completely removed from the table, there is less utilization in the transaction log, hence performance is high.

Before we proceed with the truncation of the table, let’s create a dummy table, insert some values on CusName and CusID, and test on it.

Create table dummy

(

CusID int not null,

CusName nvarchar(100) null

)

Insert into dummy values (1, 'Dale'), (2, 'James')

T-SQL command to truncate a table.

begin tran

Truncate table dummy

select * from dummy

rollback

The above command will completely remove two rows with two unique records from the dummy table.

DELETE

DELETE is a DML command, which is used to either remove all the rows from a table or a specific row from a table using the WHERE clause. As the rows are deleted from the table one by one, every single delete from the table is maintained and recorded in the transaction log. Since such records are maintained, there is high utilization of transaction log, due to which the performances are low.

Further, there is a need to DELETE permission for login in SQL Server to carryout the DELETE command. When there is a rollback possibility for Truncate, there is also a similar way to rollback DELETE records of a table by looping to Begin Transaction.

T-SQL command to delete a table.

Let’s use the dummy table to delete the CusID record.

begin tran

DELETE from dummy where CusID=1

select * from dummy