What happen when deleting records from the table or Truncating the table?

There are two way to remove the data from the table. Either we can use delete or Truncate. But keep in mind when we are using the truncate we can remove all data from the table. We can’t use the where condition in the truncate. When we need to remove all records we can use truncate. Mostly we are using truncate on the staging table. If we want to remove particular data from the table then we need to use delete statement.

Read here: Difference between DELETE and TRUNCATE

https://bageshkumarbagi-msbi.blogspot.com/2015/12/difference-between-delete-and-truncate.html

Let’s see here behind the scenes of the SQL Delete Statement & Truncate statement.

For Example here we are creating a table.

create table Employee ( EmpID int identity(1,1), Ename varchar(MAX), Eaddress varchar(MAX), EMob varchar(MAX) )

Table created

We can see the table store details with bellows views.

select * from sys.tables where name='Employee'; select * from sys.schemas

 

select * from sys.partitions partitions where object_id=98099390; select * from  syscolumns where id=98099390; select * from sys.types select * from  sys.system_internals_partition_columns  where Partition_Id = 72057594046644224 select * from  sys.allocation_units where allocation_unit_id=72057594046644224

 

These view have the information about partition and allocation unit ids where pages are present.

There is no records in the table. 

Now inserting around 500K records into this table

DECLARE @c int set @c=1 while @c<=500000 BEGIN insert into Employee values('Bagesh Kumar Singh','Noida','888880XXXX') set @c=@c+1 END

500k records inserted successfully.

27.32 MB space taken to store the data.

Now see the page information.

DBCC IND('Test_DB',employee,-1)

3498 pages created to store the data.

See the data in the pages

DBCC TRACEON(3604)

DBCC Page('Test_DB',1,568,1)

When we are inserting the records into the table all information is log in the transaction log . see below

select * from fn_dblog(null,null) where PartitionId = 72057594046709760

   

Now I am going to delete all records from this table and see what happen after deleting the records.

delete from employee;

It took around 2 seconds to delete the all records.

Now see the size of the table.

There is no changes in the size. Changes in only Row counts.

When we are deleting the records from the table all information log into the transaction log.

We have delete the 500K records.

See the package information

Still all pages are exists in the system. 

See the package level information. 

After delete the data is delete from page. This package have the information about the page id and row offset which is available in the transaction log.

In this example see Page ID is : m_pageId = (1:568)

This pages have offset of the data.

 

OFFSET TABLE:

 

Row - Offset                       

142 (0x8e) - 0 (0x0)                

141 (0x8d) - 0 (0x0)               

140 (0x8c) - 0 (0x0)               

139 (0x8b) - 0 (0x0)               

138 (0x8a) - 0 (0x0)               

137 (0x89) - 0 (0x0)               

136 (0x88) - 0 (0x0)               

135 (0x87) - 0 (0x0)                

134 (0x86) - 0 (0x0)               

133 (0x85) - 0 (0x0)               

132 (0x84) - 0 (0x0)               

131 (0x83) - 0 (0x0)  

……

 

Same information we will get it in the transaction log. 

With the help of there we can see the data from transaction log.

We will get the data from the below columns

Ø  RowLog Contents 0

Ø  RowLog Contents 1

Ø  RowLog Contents 2

Ø  RowLog Contents 3

Ø  RowLog Contents 4

Ø  RowLog Contents 5

Data are stored in this columns in the hex decimal number along with some hex number.

select [RowLog Contents 0],[RowLog Contents 1],[RowLog Contents 2], [RowLog Contents 3],[RowLog Contents 4],[RowLog Contents 5],* from fn_dblog(null,null) where PartitionId = 72057594046709760 and Operation='LOP_DELETE_ROWS' and [page ID]='0001:00000568'

Let’s see the data from the log.

select  LTRIM(RTRIM(CONVERT(VARCHAR(max),Substring([RowLog Contents 0],20,Len([RowLog Contents 0]))))) as raw_data, * from sys.fn_dblog(NULL, NULL) where  PartitionId = 72057594046709760 and Operation='LOP_DELETE_ROWS'

  

Now I am truncating the table.

Table is having 500K records.

See the page information.

Now truncating the table.

Table truncated successfully.

See the page information. 

There is no page all pages are free.

Now see the size of the table.

There is no entry in transaction log.

When we are using staging table we need to truncate that table. It is faster than delete. But in the master table if we want to delete specific records go with delete command.