Rajendra Gupta
check the records count after SQL delete

The internals of SQL Truncate and SQL Delete statements

February 21, 2020 by

This article gives you an insight into the SQL Truncate and SQL Delete commands behavior.

Introduction

In the article Difference between SQL Truncate and SQL Delete statements in SQL Server, we learned the difference between truncate and delete statements. You should go through this article before going further with this article.

In this article, we will look at the following topics.

  • Can you Rollback a delete transaction and a truncate command?
  • Which one is faster, Truncate or Delete and why?

Rollback a delete transaction

Let’s say you started two separate transactions – delete and truncate. Later, we want to roll back this transaction. We issued a rollback command for this.

Now, a question for you – Does rollback command recovers data for both delete and truncate? You might think we cannot rollback a truncate statement. Let’s explore this practically.

Prepare a database environment

Let’s create a new database [SQLShackDemo] for this article, along with a sample data table:

We have 10 records in the test table for demo purposes:

Sample data

We can use dynamic management view sys.dm_db_database_page_allocations(starting from SQL Server 2012) to list database pages belonging to the SQL table. This DMV takes the following arguments:

  • Database id: We can specify database id or use DB_ID() function to pass the current database id
  • Object id: This parameter takes object id we want to analyze. It is good to use OBJECT_ID(‘Object’) for automatically supplying this value
  • The next two arguments are Index id and partition id. If we specify NULL values, it returns information about all indexes and partitions
  • In the last argument, we can specify LIMITED or DETAILED values. Let’s use DETAILED as it gives additional information compared to the LIMITED option output

sys.dm_db_database_page_allocations output

Alternatively, we can use DBCC IND command as well and it returns almost the similar output:

DBCC IND output

In the output, we can see two pages with PagePID 94 and 256:

  • PagePID 94: It is an IAM (Index Allocation Map) page. The IAM page has NULL values for IAMFID and IAMPID columns. PageType 10 denotes IAM page
  • PagePID 256: It is a data page. PageType 1 denotes a data page

Now, let’s begin a delete transaction using the begin transaction statement:

Since we did not specify the WHERE clause in the delete statement, it removes all ten rows from the test table:

begin transaction statement

Do not commit the transaction as of now. We use the undocumented function fn_dblog to read transaction logs. You can refer to this article, How to continuously read Transaction log file data directly in a SQL Server database with fn_dblog and fn_dump_dblog to go through this command in detail.

  • Note: You should be careful using this function in the production environment. Here, we are using it for demonstration purposes.

In the same query window with existing Begin SQL transaction, execute the following command of fn_dblog. This query filters the transaction logs for delete transaction ( defined as test1 in begin transaction statement):

SQL delete statement removes individual row and logs entry for each row in the transaction log. We removed 10 rows from the test table, and output also reflects LOB_DELETE_ROWS entry for an individual row. This LOP_DELETE_ROWS operation occurs for the delete statement. We can also see a total of 11 rows in the output of fn_dblog for this delete operation:

Output of fn_dblog for delete transaction

Execute the select statement in the same transaction, and it returns zero in the output:

Now, issue a rollback statement and check for the number of rows in the test table. It returns 10 rows in the output. It shows you can rollback a delete transaction:

check the records count after SQL delete

Rollback a truncate transaction

Now, we want to check whether we can rollback a truncate statement or not. For this demo, drop the test table and recreate it with the same 10 rows from the script shared above.

Once we have an environment ready, start a new transaction and truncate table from the following script:

In the following query, we started a transaction T1 and filtered output of fn_dblog for that particular transaction:

For SQL truncate, we do not get an entry for individual rows in the transaction log. It deallocates the extent, as shown below:

SQL Truncate output

Let’s issue rollback transaction command and check records in a test table. We get our records back. Yes, it proves that we can rollback a SQL Truncate statement as well:

Verify records

SQL Server minimizes logging for truncate. To prove this point, insert more records in the test table using the following query:

We have 100 records in the test table. Now, check the transaction log records count for both truncate and delete operations using scripts specified above.

  • Transaction log records for delete: 101

    Transaction log records for delete

  • Transaction log records for truncate: 12

    Transaction log records for truncate

From the above screenshots, we can verify that as the record count increases in a table, transaction log record count also increases for delete statement. It does not change much for the truncate statement. Hence,we can see, it takes more time to delete records in comparison with truncate for a huge table. If we wish to remove all records from a table, we should use truncate because it is fast and minimize transaction log growth.

Conclusion

In this article, we explored internals of the SQL Truncate and SQL Delete statement. It also shows the reason for a fasater execution of the truncate statement in comparison with the delete statement. You should use commands as per your requirements.

Rajendra Gupta
225 Views